I've seen a lot on converting Unix time from other formats to datetime, but nothing really from microseconds. How can you SELECT a field this with a timestamp of 1470562081943371 without getting overflows with an output of YYYY-MM-DD HH:MM:SS:MM?
Asked
Active
Viewed 1,583 times
0
-
can you use bigint? – Jeremy Oct 13 '16 at 17:47
-
Yes, that's what it would be stored in. – Fastidious Oct 13 '16 at 17:48
-
so then...select it? i just created a variable of type bigint, stored the number and selected it with no problem. Not sure what your real issue is... – Jeremy Oct 13 '16 at 17:56
-
The question is how to convert it to datetime from Unix time in microseconds. I guess I didn't specify. – Fastidious Oct 13 '16 at 18:15
-
quick search reveals... http://stackoverflow.com/questions/2904256/how-can-i-convert-bigint-unix-timestamp-to-datetime-in-sql-server – Jeremy Oct 13 '16 at 18:20
-
That one is not for microseconds. Unix timestamp overflows. – Fastidious Oct 13 '16 at 18:23
1 Answers
1
Not sure if this is unit is correct, but...
Declare @UnixTime bigint = 1470562081943371
Select DateAdd(MS,round(((@UnixTime/1000000.)-(@UnixTime/1000000))*1000,0),DateAdd(SECOND,(@UnixTime/1000000),'1970-01-01 00:00:00'))
Returns
2016-08-07 09:28:01.943
Confirmed results with http://www.epochconverter.com/
John Cappelletti
- 71,300
- 6
- 42
- 62
-
@Fastidious Well that's encouraging. Le'ts hope you can confirm the time portion. If you don't care about milliseconds, just strip off the first DateAdd(MS... – John Cappelletti Oct 13 '16 at 18:43
-
-
Thanks, all confirmed. Like I said, others are not on this granularity of time that I could find. Good to know about the same on this granularity too. – Fastidious Oct 13 '16 at 18:50
-
1@Fastidious Well done. Now you get move on the next item on your list. :) – John Cappelletti Oct 13 '16 at 18:56