# Thread: convert millisecond timestamp and then sort by date

1. Registered User
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?

## "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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

4. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

5. Registered User
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

7. Registered User
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. Registered User
Join Date
Oct 2014
Posts
9
Just saw this. Trying it now

Originally Posted by Pat Phelan
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Originally Posted by TheAlmightyOS
Oh. There is. beg the DBA to switch to Oracle
You never mentioned having an unlimited budget!

-PatP

10. Registered User
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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
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

12. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Did this thread die, or are you still struggling with the problem?

-PatP

13. Registered User
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 15:01.

14. Annie's Dog Walker
Join Date
Nov 2004
Location
on the wrong server
Posts
8,842
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.

15. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

16. Registered User
Join Date
May 2005
Location
South Africa
Posts
1,369

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
, cnt=COUNT(*)
, minbi=min(bi)
from #t1
where bi>=DATEDIFF(dd,'19700101','20150301')*24.*60*60*1000
and bi< DATEDIFF(dd,'19700101','20150501')*24.*60*60*1000
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 08: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
•