Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Oct 2014
    Posts
    9

    Answered: convert millisecond timestamp and then sort by date

    I have a set of data with epoch timestamps. Purely for the sake of reporting, I need to pull the last six months of data and group it by month. I have tried searching the googles for epoch/milliseconds to datetime but I only get the MySQL or Oracle results, not MSSQL. And yes, I have been to the MSDN. I don't understand their examples. Would someone be willing to assist me with this?

  2. Best Answer
    Posted by pdreyer

    "Maybe this example can help you

    Test data used:
    Code:
    create table #t1 (bi bigint)
    insert into #t1 values 
    (1425193200016),
    (1425278884444),
    (1429608102302),
    (1429609098307),
    (1429609701691),
    (1429609812674),
    (1430458200035),
    (1432043759526),
    (1431940047746)
    And here is the query for March and April group by month :
    Code:
    select 
      mth=dateadd(mm, DATEDIFF(mm,'',dateadd(dd,bi/1000/60/60/24,'19700101')), '') 
    , cnt=COUNT(*)
    , minbi=min(bi)
    , maxdt=MAX(dateadd(ms,bi-bi/1000/60/60/24*24*60*60*1000
               ,dateadd(dd,bi/1000/60/60/24,'19700101')))
    from #t1
    where bi>=DATEDIFF(dd,'19700101','20150301')*24.*60*60*1000
      and bi< DATEDIFF(dd,'19700101','20150501')*24.*60*60*1000
    group by dateadd(mm, DATEDIFF(mm,'',dateadd(dd,bi/1000/60/60/24,'19700101')), '')
    order by mth
    Edit: Use this where clause for last 6 months
    Code:
    where bi>=DATEDIFF(dd,'19700101', DATEADD(mm, datediff(mm,'',getutcdate())-6, '') )*24.*60*60*1000
      and bi< DATEDIFF(dd,'19700101', DATEADD(mm, datediff(mm,'',getutcdate()), '') )*24.*60*60*1000
    "


  3. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Assuming that your epoch values are UTC adjusted, use:
    Code:
    DECLARE @epochInt   INT
    ,  @EpochDate       DATETIME
    
    SET @epochInt = DateDiff(s, '1970-01-01', GetUTCDate())
    
    SET @EpochDate = DateAdd(s, @epochInt, '1970-01-01')
    
    SELECT @EpochDate AS EpochDate, @epochInt AS epochInt
    If your epoch values are local based, then change GetUTCDate() to just plain GetDate().

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oh crud! You confused me with the use of Epoch and millisecond. I read the Epoch and didn't catch the millisecond, so I assumed that is what you really wanted Epoch conversion. Re-thinking the millisecond makes me think that you mean Java time base, which requires a tiny adjustment to the code:
    Code:
    DECLARE @epochInt   INT
    ,  @EpochDate       DATETIME
    
    SET @epochInt = DateDiff(ms, '1970-01-01', GetDate())
    
    SET @EpochDate = DateAdd(ms, @epochInt, '1970-01-01')
    
    SELECT @EpochDate AS EpochDate, @epochInt AS epochInt
    Sorry for the confusion, I should have read the request more carefully and not assumed as much as I did.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #4
    Join Date
    Oct 2014
    Posts
    9
    not sure what you mean by java time base, but it very well could be. It is a largeint 13 digit number. I assume it's epoch with milliseconds.

    I want to convert, pull the last six month and then group the data by month. Most of what I have found examples for online have been hourly and minutes. Nothing for years and hours.

  6. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you want to group the data by month, then your best bet would probably be to use a CTE. This CTE derives the previous six start months, and gives you the be (beginning epoch) which is the first millisecond in the month, the ee (ending epoch, which is the first millisecond that is no longer in the month), and the base date (the first day of the month). You can JOIN your table to the final select based on the be <= epoch and epoch < ee from this CTE.
    Code:
    WITH k AS (
       SELECT Cast(1000 AS BIGINT) AS k
       )
    ,  m AS (
    SELECT 1 AS m     UNION SELECT 2
       UNION SELECT 3 UNION SELECT 4
       UNION SELECT 5 UNION SELECT 6
       )
    ,  d AS (
       SELECT DateAdd(m, DateDiff(m, 0, GetDate()) - m, 0) AS bd
    ,     DateAdd(m, DateDiff(m, 0, GetDate()) - m + 1, 0) AS ed
          FROM m
       )
    ,  java_epoch AS (
       SELECT bd AS base_month
    ,     k * DateDiff(s, '1970-01-01', bd) AS be
    ,     k * DateDiff(s, '1970-01-01', ed) AS ee
          FROM d, k
       )
       SELECT *
          FROM java_epoch
          JOIN your_table_goes_here AS your
             ON (java_epoch.be <= your.epoch
             AND your.epoch < java_epoch.ee
    This ought to process efficiently and easily to get what you have described.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  7. #6
    Join Date
    Oct 2014
    Posts
    9
    I might be going about this backwards. I can find the beginning and ending of each month. Problem is taking that and converting it over to epoch to then be used to find the data I need. There must be a easier way to do this. Oh. There is. beg the DBA to switch to Oracle

  8. #7
    Join Date
    Oct 2014
    Posts
    9
    Just saw this. Trying it now

    Quote Originally Posted by Pat Phelan View Post
    If you want to group the data by month, then your best bet would probably be to use a CTE. This CTE derives the previous six start months, and gives you the be (beginning epoch) which is the first millisecond in the month, the ee (ending epoch, which is the first millisecond that is no longer in the month), and the base date (the first day of the month). You can JOIN your table to the final select based on the be <= epoch and epoch < ee from this CTE.
    Code:
    WITH k AS (
       SELECT Cast(1000 AS BIGINT) AS k
       )
    ,  m AS (
    SELECT 1 AS m     UNION SELECT 2
       UNION SELECT 3 UNION SELECT 4
       UNION SELECT 5 UNION SELECT 6
       )
    ,  d AS (
       SELECT DateAdd(m, DateDiff(m, 0, GetDate()) - m, 0) AS bd
    ,     DateAdd(m, DateDiff(m, 0, GetDate()) - m + 1, 0) AS ed
          FROM m
       )
    ,  java_epoch AS (
       SELECT bd AS base_month
    ,     k * DateDiff(s, '1970-01-01', bd) AS be
    ,     k * DateDiff(s, '1970-01-01', ed) AS ee
          FROM d, k
       )
       SELECT *
          FROM java_epoch
          JOIN your_table_goes_here AS your
             ON (java_epoch.be <= your.epoch
             AND your.epoch < java_epoch.ee
    This ought to process efficiently and easily to get what you have described.

    -PatP

  9. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by TheAlmightyOS View Post
    Oh. There is. beg the DBA to switch to Oracle
    You never mentioned having an unlimited budget!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #9
    Join Date
    Oct 2014
    Posts
    9
    I can not seem to get the script to run. I am going to try again to describe the problem. I usually have issues in that regard.

    I have a table I want to pull data from. For whatever reason, the DBA decided to use 13 digit integer as the timestamp. Assuming epoch with milliseconds. I need to pull six months of data one month at a time so the results can be fed through one of my batch scripts to generate a report. If I can pull all six and group them all the better.

    Oh, we got them all here. Sybase, Oracle, MySQL, and MSSQL. I wish they would just pick one and stick with it.

  11. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This demo is a bit more elegant and shows a demo table of epoch values and a simple query against it.
    Code:
    DECLARE @t TABLE (                                  -- Demo table of epoch values
       epoch        BIGINT
       )
    
    INSERT INTO @t (epoch)                              -- Several years of values
       SELECT Cast(1000 AS BIGINT) * DateDiff(s
    ,    '1970-01-01', DATEADD(d, -number, GetDate()))
          FROM master.dbo.spt_values
          WHERE  'P' = type
    
    ; WITH k AS (                                       -- Constant ms per sec
       SELECT Cast(1000 AS BIGINT) AS k
       )
    ,  m AS (                                           -- Month iterator
    SELECT 1 AS m     UNION SELECT 2
       UNION SELECT 3 UNION SELECT 4
       UNION SELECT 5 UNION SELECT 6
       )
    ,  d AS (                                           -- Date cooker
       SELECT DateAdd(m, DateDiff(m, 0, GetDate()) - m, 0) AS bd
    ,     DateAdd(m, DateDiff(m, 0, GetDate()) - m + 1, 0) AS ed
          FROM m
       )
    ,  java_epoch AS (                                  -- Java epoch values
       SELECT bd AS base_month
    ,     k * DateDiff(s, '1970-01-01', bd) AS be
    ,     k * DateDiff(s, '1970-01-01', ed) AS ee
          FROM d, k
       )
       SELECT java_epoch.base_month                     -- Simple query against demo
    ,     COUNT(*) AS days_in_month
          FROM java_epoch
          JOIN @t AS your
             ON (java_epoch.be <= your.epoch
             AND your.epoch < java_epoch.ee)
          GROUP BY java_epoch.base_month
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Did this thread die, or are you still struggling with the problem?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  13. #12
    Join Date
    Oct 2014
    Posts
    9
    I was away during the weekend.

    In my experience threads have to be inactive for months to be considered "dead".

    I can not make any changes to the DB. I am a read only user so I can not run the example you gave me.

    I need a query that gives me data for the last six months, grouped by month. If I can do this without converting the time stamp that would be fine too. Then feed that into shell script and out comes nicely formated tables, charts and graphs.
    Last edited by TheAlmightyOS; 05-18-15 at 16:01.

  14. #13
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Pats last code post only requires read only access. Its a select query with a table variable and some cte's. I just ran it on a read only database.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  15. #14
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The sample that I provided doesn't require any special privileges. It doesn't change anything at all in the database, it only declares and uses a table variable for the demonstration.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  16. #15
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Maybe this example can help you

    Test data used:
    Code:
    create table #t1 (bi bigint)
    insert into #t1 values 
    (1425193200016),
    (1425278884444),
    (1429608102302),
    (1429609098307),
    (1429609701691),
    (1429609812674),
    (1430458200035),
    (1432043759526),
    (1431940047746)
    And here is the query for March and April group by month :
    Code:
    select 
      mth=dateadd(mm, DATEDIFF(mm,'',dateadd(dd,bi/1000/60/60/24,'19700101')), '') 
    , cnt=COUNT(*)
    , minbi=min(bi)
    , maxdt=MAX(dateadd(ms,bi-bi/1000/60/60/24*24*60*60*1000
               ,dateadd(dd,bi/1000/60/60/24,'19700101')))
    from #t1
    where bi>=DATEDIFF(dd,'19700101','20150301')*24.*60*60*1000
      and bi< DATEDIFF(dd,'19700101','20150501')*24.*60*60*1000
    group by dateadd(mm, DATEDIFF(mm,'',dateadd(dd,bi/1000/60/60/24,'19700101')), '')
    order by mth
    Edit: Use this where clause for last 6 months
    Code:
    where bi>=DATEDIFF(dd,'19700101', DATEADD(mm, datediff(mm,'',getutcdate())-6, '') )*24.*60*60*1000
      and bi< DATEDIFF(dd,'19700101', DATEADD(mm, datediff(mm,'',getutcdate()), '') )*24.*60*60*1000
    Last edited by pdreyer; 05-20-15 at 09:00. Reason: Where clause for 6 months

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •