Convert UNIX Timestamps to DATE/TIMEs in SQL Server - Part#1
-
Comments posted to this topic are about the item Convert UNIX Timestamps to DATE/TIMEs in SQL Server - Part#1
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Sidebar/Errata:
It doesn't affect any of the outcomes in this article, but I said the following in the article...
Since the numbers above are BIGINTs and the “magic number” is an INT, if we divide the former by the latter, our answer will be returned as BIGINT with no remainder...
That's NOT actually true despite what Microsoft says...
The truth is, the result we be a NUMERIC(25,9) datatype for the given positive number example in the article. You can prove that with the following code...
SELECT Result = 253402300799999/86400000
,BaseType = SQL_VARIANT_PROPERTY(253402300799999/86400000,'BASETYPE')
,Precision = SQL_VARIANT_PROPERTY(253402300799999/86400000,'PRECISION')
,Scale = SQL_VARIANT_PROPERTY(253402300799999/86400000,'SCALE')
;Results from above...
SELECT Result = 253402300799999%86400000
,BaseType = SQL_VARIANT_PROPERTY(253402300799999%86400000,'BASETYPE')
,Precision = SQL_VARIANT_PROPERTY(253402300799999%86400000,'PRECISION')
,Scale = SQL_VARIANT_PROPERTY(253402300799999%86400000,'SCALE')
;Results from above...
Very fortunately, the INTeger part of both of those returns converts to an INT datatype with no error and no rounding.
I did have folks do some additional checking for all versions from SQLServer 2000 thru 2022. They all have the same issue. I've submitted a "bug" to MS on the problem. Considering how long the bug has existed (at least according to the definition of the word "integer", which shouldn't be mistaken as a DataType as it has been used in the two links I provided above), I expect the best Microsoft will do is update the documentation rather than observing the real rules for such "Integer Math".
Yep... I know... they didn't have BIGINT in 2000. The results were the same though. I'm thinking that when they came out with BIGINT, they didn't make the change. They just left things the way they were in 2000 where numeric constants that large automatically took the form of a large NUMERIC value.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
- SwePeso wrote:
https://www.sqltopia.com/unix-timestamp/
Thanks, Peter... Those are the basic formulas we ended up with in the article. They're just "DRY"ed out a bit in the article. And, to be sure, you're NOT one of the one's that I was talking about being wrong.
One of the particularly agonizing ones I ran across was by an SQL Server MVP. Someone provided the same formula as you have above as the correct way to do things and cited the rounding problem that I cited in the article. The MVP has made no changes nor has even included a note that mentions the rounding issue.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Very nice and informative article, but I miss one important point:
Unix timestamps are based on the UTC timezone (this may be clear for you but not for a random junior dev), while most of the world population lives in other timezones.
So you may want to rename TheDateTime to UTCDateTime (to make it more clear) and - if you don't want / need it to be saved as UTC datetime - do an additional (CEST is only used as example)
CAST(calc.UTCDateTime AT TIME ZONE 'UTC' at TIME ZONE 'Central European Standard Time' AS DATETIME2(3)) as CESTDateTime
God is real, unless declared integer.
-
Thanks for the feedback, Thomas. I'll have to leave that up to the individual user because, although close, UNIX TimeStamps are not quite the same as UTC even if people treat them as such. See below...
From: https://en.wikipedia.org/wiki/Unix_time
Unix time is not a true representation of UTC, because a leap second and the second before it have the same Unix time (or after it, implementation dependent). Put differently, every day in Unix time contains exactly 86400 seconds;[2] no seconds added to or subtracted from the day as a result of positive or negative leap seconds.
I also didn't want to get into that discussion because it would be a discussion similar to the food fights about NULL. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thank you, Jeff, for this fine article.
I found your approach very interesting as it is quite different from mine.
😎
Here are the two functions I use for converting Unix TimeStamp to DATETIME2(3) and the other way around.
USE TEEST;
GO
SET NOCOUNT ON;
GO
CREATE OR ALTER FUNCTION dbo.ITVF_UXTS_TO_DT2
(
@UXTimeStamp BIGINT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT
CONVERT(DATETIME2(3),CONVERT(VARBINARY(8),CONCAT(0x03,REVERSE(CONVERT(VARBINARY(4), (@UXTimeStamp % 86400000),0)),REVERSE(CONVERT(VARBINARY(3),((@UXTimeStamp / 86400000) + 719162),0)))),0) AS WinDateTime2;
GOUSE TEEST;
GO
SET NOCOUNT ON;
GO
CREATE OR ALTER FUNCTION dbo.ITVF_DT2_TO_UXTS
(
@DT2DATE DATETIME2(3)
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
SELECT
DATEDIFF_BIG(MS,CONVERT(DATETIME2(3),'0001-01-01 00:00:00:000',121),@DT2DATE) - 62135596800000 AS UXTimeStamp;
GOAnd a quick test
USE TEEST;
GO
SET NOCOUNT ON;
GO
DECLARE @UXTimeStamp BIGINT = DATEDIFF_BIG(ms,'1970','2021-12-31 23:59:59:999');
SELECT
@UXTimeStamp AS UXTS
,WT.WinDateTime2
FROM
dbo.ITVF_UXTS_TO_DT2(@UXTimeStamp) WT;
GO
DECLARE @DT2DATE DATETIME2(3) = CONVERT(DATETIME2(3),'2021-12-31 23:59:59:999',121);
SELECT
W2U.UXTimeStamp
FROM dbo.ITVF_DT2_TO_UXTS(@DT2DATE) W2U;Funny enough, I did draft an article on the subject some time back but, as with so many subjects, work got in the way.
Results
UXTS WinDateTime2
-------------------- -------------------------
1640995199999 2021-12-31 23:59:59.999
UXTS UXTimeStamp
-------------------- -------------------------
1640995199999 1640995199999 -
Thank you for the kind feedback and the functions, Eirikur.
Perhaps I'm using the function incorrectly but try the following (and, if so, please let me know)...
SELECT * FROM dbo.ITVF_UXTS_TO_DT2(-1)
... which should return the date and time of 1969-12-31 23:59:59.999. Instead, it returns the following...
Msg 241, Level 16, State 1, Line 15
Conversion failed when converting date and/or time from character string.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Just a few more tests on the function you posted. Haven't done a deep dive on your code but, at first blush, it would appear that it doesn't like working with negative millisecond parts of the day.
--===== These work fine
SELECT * FROM dbo.ITVF_UXTS_TO_DT2(86400001);
GO
SELECT * FROM dbo.ITVF_UXTS_TO_DT2(86400005);
GO
SELECT * FROM dbo.ITVF_UXTS_TO_DT2(86400999);
GO
--===== These also work fine.
SELECT * FROM dbo.ITVF_UXTS_TO_DT2(-86400000);
GO
SELECT * FROM dbo.ITVF_UXTS_TO_DT2(-172800000);
GO
SELECT * FROM dbo.ITVF_UXTS_TO_DT2(0);
GO
--===== These produce errors
SELECT * FROM dbo.ITVF_UXTS_TO_DT2(-1);
GO
SELECT * FROM dbo.ITVF_UXTS_TO_DT2(-86400001);
GO
SELECT * FROM dbo.ITVF_UXTS_TO_DT2(-86400005);
GO
SELECT * FROM dbo.ITVF_UXTS_TO_DT2(-86400999);
GO--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Good point Jeff,
never used the functions for negative timestamps, will have to adjust the logic as the binary structure wrecks havoc on the bit-bashing approach, -1 being 0xFFFFFFFFFFFFFFFF, -2 = 0xFFFFFFFFFFFFFFFE etc. The other way around works fine as it is simply a simple arithmetic operation.
😎
Got an idea on how to resolve this by shifting the base, in other words, moving the relative goal posts, let's chat about this on Friday.
The thing I really like is the performance, although I haven't tested against your fine code, I'll rig up a framework when I'll have some time.
-
@Eirikur: of course it is possible to do the calculation with bit shifting, reversing, converting to binary etc., but WHY? Just because it is possible and makes some old assembler guys happy or is it really faster than Jeff's version?
God is real, unless declared integer.
- Eirikur Eiriksson wrote:
Good point Jeff, never used the functions for negative timestamps...
Unfortunately and as identified in point #2 of the Prolog of the article...
2. Many solutions don’t account for negative UNIX Timestamps, which are used to represent dates and times prior to 1970-01-01 (01 Jan 1970), which is also known as the UNIX "Epoch".
... that's one of the reasons I wrote the article. 😀
Eirikur Eiriksson wrote:...will have to adjust the logic as the binary structure wrecks havoc on the bit-bashing approach, -1 being 0xFFFFFFFFFFFFFFFF, -2 = 0xFFFFFFFFFFFFFFFE etc. The other way around works fine as it is simply a simple arithmetic operation. 😎 Got an idea on how to resolve this by shifting the base, in other words, moving the relative goal posts, let's chat about this on Friday. The thing I really like is the performance, although I haven't tested against your fine code, I'll rig up a framework when I'll have some time.
That's what I like about the community on SQL Server Central... different ideas. I've seen you do some pretty cool things with 0x7FFF (IIRC) back on a "digits only" function with a remarkable bit of extra performance compared to other methods. It'll be interesting to see if such a performance improvement can be gained here.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Thomas Franz wrote:
@Eirikur: of course it is possible to do the calculation with bit shifting, reversing, converting to binary etc., but WHY? Just because it is possible and makes some old assembler guys happy or is it really faster than Jeff's version?
BWAAA-HAAA-HAAA!!! Had it been anyone other than Eirikur or a very small handful of other souls, I'd have jumped down their throats and teetered on their wishbone while throwing lit packages of matches at their stomachs all while yelling "Show me the money!" at the top of my lungs. 😀
I have, however, seen Eirikur pull the ol' "assembler" tricks out of his hat in a couple of places (especially on a long thread about a "digits only" function, where it did make things substantially faster). If he wants to give it a go and, me being an old assembler junkie (actually, I used to have the 6502 uProcessor instruction set memorized and didn't need an Assembler even for calculating branch distances), I would love to see him pull it off even it it were slower. I'd also be absolutely delighted if it were faster. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Just had a look at the negative timestamp values and...
Too simple!
😎
Here is the code:
USE TEEST;
GO
SET NOCOUNT ON;
GO
CREATE OR ALTER FUNCTION dbo.ITVF_UXTS_TO_DT2
(
@UXTimeStamp BIGINT
)
RETURNS TABLE
WITH SCHEMABINDING
AS
/*
---------------------------------------------------------------------
TODO: Complete commenting!
---------------------------------------------------------------------
Constructing a DATETIME2(3) binary compatible value for a conversion
from BIGINT.
---------------------------------------------------------------------
The DATETIEM2(3) construct:
---------------------------------------------------------------------
HEX 0x | IGNORE
PRECISSION 03 | FIXED FOR (3), 1 byte added if precission is defined, 0x00 defaults to precission of (7)?
TIME B4B3B2B1 | BIGENDIAN 4 BYTES
DATE B3B2B1 | BIGENDIAN 3 BYTES
---------------------------------------------------------------------
Minimal input value: -62135596800000 0001-01-01 00:00:00:000
Maximum input value: 253402300799999 9999-12-31 23:59:59:999
---------------------------------------------------------------------
*/
RETURN
WITH TIME_BASE(TIMEBASE) AS
---------------------------------------------------------------------
-- Shifting the base from the "UNIX Epoch" to the "Windows Epoch" by
-- using the difference between the two in milliseconds.
---------------------------------------------------------------------
(
SELECT
CONVERT(BIGINT,(@UXTimeStamp + CONVERT(BIGINT,62135596800000,0)),0) AS TIMEBASE
)
SELECT
---------------------------------------------------------------------
-- High level conversion of a big endian binary value to DATETIME2
---------------------------------------------------------------------
CONVERT
(
DATETIME2(3)
,CONVERT
(
VARBINARY(8)
,CONCAT
(
0x03
,REVERSE
(
CONVERT
(
VARBINARY(4)
,(TB.TIMEBASE % 86400000)
,0
)
)
,REVERSE
(
CONVERT
(
VARBINARY(3)
,((TB.TIMEBASE / 86400000))
,0
)
)
)
)
,0
)
AS WinDateTime2
FROM TIME_BASE TB
GO -
Gosh, Eirikur... now you've got me asking the same as what Thomas asked...
That's an awful lot of implicit (Reverse) and explicit conversions just to handle some negative time stamps only to resort to the same integer math as what was presented in the article, which also ultimately stored it as that same binary form behind the scenes. With that, I have to ask, is it faster? What is the advantage of the complexity here other than to impress the "assembly language affection-ado" in a fellow old fart like me or demonstrate a good knowledge of the "0001" Epoch and the underlying "Big Endian" mess MS made of things behind the scenes?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply