tl;dr
A complete and documented function is available at the end of the article.
You don't know what you're missing though. 😉
"Thank You"
Thank you, Mr. Steve Jones, for all the work you do for this wonderful community of SQL Professionals and for keeping this site viable and strong. I also thank Phil Parkin for the incredible proof reading job he did. It's amazing how much one can miss when proof-reading their own writings and he caught it all. Thank you, Phil!
Prologue
A decade ago, it was a fairly rare thing for most of us in the SQL Server world to have to convert a UNIX Timestamp to a human readable date/time or vice versa in SQL Server. It’s no longer a rare thing and, according to posts on many different forums, lots of people have had the need or will do so in the near future.
However, if we do this search in Google, we’ll find a ton of solutions…
So, why do we need yet another article on the subject? Simple...
- A lot of the solutions don’t account for the “2038/Friday the 13th” problem. A brief explanation is that the 2nd operand of DATEADD must be an INT and we overrun that by just 1 second after the date/time of “2038-01-19 03:14:07”
- 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".
- Many of the solutions are flat out wrong because of rounding errors that creep into the picture due to implicit datatype conversions even when the target datatype is ultimately DATETIME2(3). Others incorrectly use the LEFT() function to isolate seconds, which produces incorrect answers the closer we come to or the further away we get from the Epoch Date because the scale of the value changes by 1 or more digits..
- And, to handle the “2038” problem, the “new, most common” UNIX Timestamp is now a BIGINT containing the number of milliseconds, which compounds all of the problems previously mentioned above, especially the part about being wrong due to rounding.
The story behind UNIX Timestamps is interesting but long and complicated, so we’re not going to dig into any history. Instead, we’re going to concentrate on what UNIX Timestamps are and how to convert them to the DATETIME(3) datatype while avoiding the “gotchas”.
Part #2 of this two part series will demonstrate additional rounding issues when converting “New” type of UNIX Timestamps to the DATETIME datatype.
This is a bit of a long article, with a large number of steps that are meant only as “proofs” that it works as advertised. As I stated in a previous article, “Explaining simplicity is sometimes a complex task”.
What is a UNIX Timestamp?
It’s a whole number that represents a date and time. There are two common representations and both are based on the same “Epoch”.
What is an “Epoch”?
Heh… it could be the answer to a “Jeopardy” question of “What noise does someone make just before someone else says ‘gesundheit’”? 😊
According to the 3rd definition of the word “Epoch” provided by the Merriam-Webster.com Dictionary (https://www.merriam-webster.com/dictionary/epoch) , it’s “an instant of time or a date selected as a point of reference”.
What is frequently referred to as the “UNIX Epoch” is the first instant of the 1st of January, 1970 in the Gregorian Calendar (ref: https://en.wikipedia.org/wiki/Unix_time). This is simply a reference date where all dates and times are represented by some form of positive integer after the “Epoch” date and a negative integer before the “Epoch” date. The exact moment of the “Epoch” date, which is “01 Jan 1970 00:00:00.0000000”, is represented by the integer zero.
In other words, it’s simply a count of the number of given intervals (seconds or milliseconds, in the case of two most common forms of UNIX Timestamps) that any given date/time is away from the “Epoch” date and time.
Without getting into a discussion about “Leap Seconds”, the fact that UNIX Timestamps are NOT based on pure UTC, and a bunch of other complications that most folks don’t need to account for, let’s get into the two more common types of UNIX TimeStamps.
The “Old” Type of UNIX Timestamp (INT)
The “Old” type of UNIX Timestamp is based on the number of seconds before or after the Epoch of 01 Jan 1970. Positive numbers are the number of seconds a date occurs AFTER the Epoch and negative numbers are the number of seconds a date occurs BEFORE the Epoch.
The “Old” type is usually provided as a simple 4 byte INTeger.
The “New” Type UNIX Timestamp (BIGINT)
The most common “New” type of UNIX Timestamp is based on the number of milliseconds before or after the Epoch of 01 Jan 1970. Positive numbers are the number of milliseconds a date occurs AFTER the Epoch and negative numbers are the number of milliseconds a date occurs BEFORE the Epoch.
The “New” type is usually provided as an 8 byte BIGINT.
There are variations for microseconds and nano seconds, but we're not going to cover those in this article. You will, however, be able to figure those out on your own, based on the techniques covered in this article.
The “2038/Friday the 13th” Problem
The problem with the “Old” type of UNIX Timestamp is that it’s based on an INTeger. If we add the largest possible positive integer value (as seconds) to the Epoch date and do the same for the most negative possible value using the following code, we begin to see the need for the change to the “New” 8 byte type of UNIX Timestamp.
--===== Demonstrate the DATETIME limits of the "Old" UNIX TimeStamp Type.
SELECT LatestUNIXTimeStampINT = DATEADD(ss, 2147483647,'01 JAN 1970')
,EarliestUNIXTimeStampINT = DATEADD(ss,-2147483648,'01 JAN 1970')
;Results:
If you look at the “LatestUNIXTimeStampINT” column, you’ll see why they call it the “2038 Problem”. Date/times later than that are not possible using the “Old” 4 byte type of UNIX Timestamp. It’s also known as the “Y2K38” problem for the same reason.
The reason why it’s sometimes called the “Friday the 13th” problem is that, when the underlying integer “rolls over” to the beginning of the INTeger series (the most negative integer possible), the representative date just happens to occur on a “Friday the 13th”. You can see that date/time in the EarliestUNIXTimeStampINT column in the same results above.
Soooo… they shifted to the use of an 8 byte BIGINT . It turns out that if they kept using seconds, like they did in the "Old" 4 byte INTeger version, that would allow dates and times both before and after the UNIX Epoch of approximately 292 billion years, “which is over twenty times the present age of the universe " (ref: https://en.wikipedia.org/wiki/Unix_time). Taking advantage of the sheer scale of BIGINTs, they decided to base the “New” type UNIX Timestamp on “milliseconds before and after the Epoch”.
And THAT, my friends, is where all the points of agony and errors mentioned in the Prologue of this article start because, in SQL Server, there is no DATEADD_BIG and the DATETIME datatype is not precise to the millisecond.
Technique: Abbreviating the First of the Year in Formulas
First, we’re going to cheat a bit. As text, the UNIX Epoch is '1970-01-01' or any of the other text renditions for that date. That's kind of long and so we’re going to cheat and use a 4-character version of the UNIX Epoch date as just ‘1970’ from here on. You can represent the first of any year in your temporal formulas just by including the year number as a 4 character string.
The following code proves that the abbreviation of '1970' is just fine as long as you remember that it MUST be a string surrounded by single quotes.
--===== Prove that '1970' is the same as '1970-01-01'
SELECT DateOnlyShortcut = CONVERT(DATE ,'1970')
,DatetimeShortCut = CONVERT(DATETIME ,'1970')
,Datetime2ShortCut = CONVERT(DATETIME2,'1970')
;Here are the results from that code. All three of those datatypes do the conversion correctly behind the scenes.
Convert Date/Time to a UNIX Timestamp
This will probably be one of the simplest temporal conversions that you’ll ever do. For either the “Old” (INT based on seconds) or the “New” (BIGINT based on milliseconds) UNIX Timestamps, just take the difference between the UNIX Epoch date (01 Jan 1970) and the date/time to be converted. Using a given date/time of “2022-07-19 17:59:30.853” as an example, here’s how to convert it to the “Old” type (based on seconds) and the “New” type (based on milliseconds).
--===== Convert a given Date/Time to the "Old" and "New" UNIX Timestamps.
DECLARE @GivenDateTime DATETIME = '2022-07-19 17:59:30.853'
;
SELECT OldUnixTS=DATEDIFF (ss,'1970',@GivenDateTime) --Always based on Seconds
,NewUnixTS=DATEDIFF_BIG(ms,'1970',@GivenDateTime) --Based on Milliseconds, in this case.
;Here are the results from that code:
There are two things to notice in those results…
The first is the fact that we had to use DATEDIFF_BIG for the NewUnixTS value because the resulting number of milliseconds is larger than what will fit into a 4 byte INT datatype. The resulting datatype for DATEDIFF is INT and the resulting datatype for DATEDIFF_BIG is BIGINT.
The second thing to notice is that they both produce the exact same number of seconds (Red Boxes). The NewUnixTS has an additional 3 digits (Blue box) to cover the milliseconds, which are simply not present in the OldUnixTS.
We haven't covered "Negative" UNIX Timestamps (yet), which represent dates and time prior to the Epoch date of '1970', but they work exactly the same way. The important thing to remember is that the "startdate" (2nd parameter of DATEDIFF and DATEDIFF_BIG) must ALWAYS be '1970' or some equivalent.
Convert the “Old” Type of UNIX Timestamp to Date/Times
It's super easy to convert an "Old" INTeger based UNIX Timestamp back to a Date/Time. Just add the value (as seconds) of the "Old" type of UNIX Timestamp to the Epoch date using DATEADD.
Here's a coded example. We're using the UNIX Timestamp value that we created in the previous section above just so you can see that both the conversion from a Date/Time and the conversion back to a Date/Time works correctly.
--===== Convert "Old" Type of UNIX Timestamp Back to Date/Time DECLARE @OldUnixTS INT = 1658253570 --Unix Timestamp From previous section SELECT DateTime = DATEADD(ss,@OldUnixTS,'1970') ;
Results:
I can't stress enough that this method of converting a UNIX Timestamp to a Date/Time only works for the “Old” type of UNIX Timestamp. Converting a “New” type of UNIX Timestamp back to a Date/Time requires a bit more complexity.
An Example of Doing It Wrong
Here’s an introduction to the agony and the source of errors when converting the “New” type of UNIX Timestamps back to a Date/Time. They’re NOT INTs! They’re BIGINTs and DATEADD can only handle INTs. I don’t know what Microsoft was thinking when they created a DATEDIFF_BIG function but did NOT create a DATEADD_BIG function.
If you search for such solutions using the following link, you'll find many and, like I said, some are flat-out wrong.
https://www.google.com/search?q=convert+unix+timestamp+to+datetime+in+sql+server
Let’s first see one example of what I’m talking about…
If we take a fairly recent date and time and convert it to the “New” type of UNIX Timestamp using the following code…
--===== Convert a data/time to a "New" UNIX Timestamp of milliseconds. SELECT NewUnixTS = DATEDIFF_BIG(ms,'1970','2021-12-31 23:59:59:999') ;
… we get the following value for a “New” UNIX Timestamp:
Now, let’s convert that value back to a date and time using one of the methods that I found in an article on the internet. Of course, we can’t represent milliseconds that end with a “9” in DATETIME, so the author of this example uses DATETIME2(3) as the final datatype:
--===== One of many examples that do the conversion incorrectly.
DECLARE @NewUnixTs BIGINT = 1640995199999;
SELECT BadDateTime = CAST(DATEADD(ms
,CAST(RIGHT(@NewUnixTs,3) AS SMALLINT)
,DATEADD(ss,@NewUnixTs/1000,'1970')) AS DATETIME2(3))
;Here’s the result we get from that and OOOOOOOOHHHH MMMMYYYYYYY! It doesn’t look anything at all like the original date of '2021-12-31 23:59:59:999'.
Every DATEPART rounded up! It’s not in the same day, month, or even year anymore!
The reason why the rounding occurred is because the inner DATEADD created a DATETIME datatype and any time ending with a “9” in milliseconds will round up to the next even “10” milliseconds. Since all of the DATEPARTs were at their max, they ALL rounded up taking us all the way into the next year!
It also doesn't work for date/times that exceed either of the Y2K38 cutoff dates despite the final conversion to DATETIME2(3). The reason, again, is because of the inner DATEADD. It's adding seconds and the maximum value for an INT used as seconds will only take us out to the Y2K38 cutoff dates.
Convert "New" Type of UNIX Timestamp to DATETIME2(3)
Ok… so we have a couple of problems on our hands. The biggest problem is that there’s no DATEADD_BIG function. We need to change our thinking...
Step 1: Convert Milliseconds to Days Instead of Seconds
We are constrained by the fact that DATEADD can’t handle anything larger than an INT. Let’s first see how many DAYS there are between 01 Jan 0001 and the Unix Epoch of 01 Jan 1970. Let’s also find out how many DAYS there are between 01 Jan 1970 and 12 Dec 9999. If neither of them violate the boundaries of an INT, we’ll be golden for that part of the problem.
How Many Milliseconds in a Day?
Since the “New” type of UNIX TimeStamp that we're using is in milliseconds, we first need to find out how many milliseconds there are in a day. We could do the math, but let’s use DATEDIFF to tell us the answer instead:
--===== Calculate how many milliseconds in 1 day. SELECT msPerDay = DATEDIFF(ms,'01 Jan 2022','02 Jan 2022') ;
And that gives us the “magic number” for all this…
While that’s a fairly big number, it’s less than an INT (had to be because DATEDIFF returns an INT) BUT, if it wasn't, we could simply use DATEDIFF_BIG and use the return as a BIGINT because, as you'll soon see, we'll be using this number as a divisor and not as a standalone value in DATEADD.
Calculate MAX Days TO and FROM the UNIX Epoch as More Proof
If we take any “New” type of UNIX Timestamp and evenly divide it by 86400000 (the “magic number", for calculating milliseconds per day), we’ll get the number of days away from the UNIX Epoch. If that’s less than the MAX value for an INT, for both the earliest and latest dates possible in SQL Server, we’re golden for the use of DATEADD.
The earliest date available in SQL Server is 01 Jan 0001 and the latest date is 31 Dec 9999. Let’s simply use DATEDIFF to see how many days each is from the UNIX Epoch of 01 Jan 1970.
--===== Calculate the distance in days from the MIN and MAX dates possible.
SELECT EarliestDateDays = DATEDIFF(dd,'01 Jan 1970','01 Jan 0001')
,LatestDateDays = DATEDIFF(dd,'01 Jan 1970','31 Dec 9999')
;Results… NEITHER is outside the range of an INT! We're golden for the use of DATEADD later on!
Creating Timestamps for Testing
Now let’s convert the min and max dates of SQL Server, with some test times, to UNIX Timestamps and then we’ll try to convert those back to just the date part. I’m going to use 23:59:59.999 as times for both dates.
--===== Convert the Min and Max dates possible (with a time element) to UNIX TimeStamps.
-- Remember, we are working with milliseconds here.
SELECT EarliestUnixTS = DATEDIFF_BIG(ms,'1970','01 Jan 0001 23:59:59.999')
,LatestUnixTS = DATEDIFF_BIG(ms,'1970','31 Dec 9999 23:59:59.999')
;And that gives us the following number of milliseconds for the two dates, which IS the “New” UNIX Timestamp for each of the two dates that we're going to test with.
Convert the Days to Dates Using "Integer Math"
Now, let’s plug those numbers into a bit of division along with our “magic number” of 86400000 milliseconds per day. 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 and no rounding and that represents the “whole” number of days we’re away from the Epoch for each of the test timestamps. Since the results of the division will fit into an INT, DATEADD will convert them to an INT for us.
While we’re at it, our formulas are getting a little long so let’s start using some obviously-named Table Value Constructors (https://docs.microsoft.com/en-us/sql/t-sql/queries/table-value-constructor-transact-sql) and obviously named variables to make things a bit clearer.
What we're doing here is using an Integer Math Divide by the number of milliseconds in a day and that gives us the number of "Whole" days we are from the Epoch. Then we just use DATEADD to add that number of days to the Epoch Date and we get the correct dates.
--===== Convert the UNIX TImestamps to whole days and add them to the UNIX Epoch
-- date to convert them to a DATETIME2(3).
DECLARE @EarliestUnixTS BIGINT = -62135510400001
,@LatestUnixTS BIGINT = 253402300799999
;
SELECT EarliestDate = DATEADD(dd,@EarliestUnixTS/msPerDay,Epoch)
,LatestDate = DATEADD(dd,@LatestUnixTS /msPerDay,Epoch)
FROM (VALUES(86400000,CONVERT(DATETIME2(3),'1970')))v1(msPerDay,Epoch)
;
It works!!! We successfully extracted the “WHOLE” dates from the UNIX Timestamps!
Now Hold on a Minute!
Ok… I said that works, but the EarliestDate came out as 02 Jan 0001 instead of 01 Jan 0001. What’s up with that?
Remember what we calculated… we calculated the number of “WHOLE” days that we’re away from the Epoch. For “positive dates and times” (greater than the Epoch date) like “9999-12-31 23:59:59.999”, it’s real easy to understand that “9999-12-31” is the last “Whole” date. All we had to do was drop the time.
Using “negative dates and times”, it’s a little more difficult to understand at first, but it works exactly the same way under the covers…
Look at the UNIX Timestamp for “9999-12-31 23:59:59.999”, which is 253402300799999. Since it’s in milliseconds, you can actually see the last 3 digits of “999” for the milliseconds. Another way to think about it is that it’s some number of whole days, which takes us out to a date of “9999-12-31” PLUS an additional “23:59:59.999” (but in milliseconds) as a part of a day, away from the Epoch. All we had to do was drop the time (using Integer Math Division) and we have the number of “whole” days.
If we look at the UNIX Timestamp for “0001-01-01 23:59:59.999”, we get the negative number of “-62135510400001”. Look at the right 3 digits of that! Where’s the “999”? Why is it “001”, instead?
Remember that the negative numbers also tell us how far away we are from the Epoch of “0” (zero). We are a certain number of “whole” days LESS than the Epoch. For the time portion, a time of “23:59:59.999” is only 1 millisecond from becoming the next day, which is a “whole” day and that day is the next day of 02 Jan 0001.
In other words, to get the whole number of days for negative UNIX Timestamps, we need to drop the time with Integer Math Division just like we did before. The thing is, the underlying date serial number is -719161.0000000115 (trust me) where the number to the left of the decimal point is the “whole” number of days and the number to the right of the decimal point represents the time of one millisecond more negative than that “whole” day. If we drop the time, we end up with the "Whole" number -719161 days which, if you look at the drawing below, represents the date of 02 Jan 0001. Time increases from left to right by getting LESS negative or More positive... just like the dates and times greater than the Epoch. (The Blue arrow shows how we go back in time from the Epoch date, producing negative Date Serial Numbers).
You don’t have to worry about any of that, though. SQL Server takes care of all that when we use Integer Math to divide the UNIX Timestamp by 86400000, which produces the number of negative “whole” days that we’re away from the Epoch date (Date Serial Number of “0”)
Step 2: Convert Leftover Milliseconds to Time
We used Integer Math Division to divide the milliseconds in the “New” type of UNIX Timestamps by the number of milliseconds in a day. Because it was Integer Math Division, it worked like 5th grade math where we ended up with a whole number for the Quotient of the Division, which was the number of “WHOLE” days our UNIX Timestamp represented. We simply discarded the other whole number, which was the Remainder and that's the "time" part of the date as a whole number of milliseconds.
Isolating the Remainder to Get the Time
That “Remainder” is the number of milliseconds that isn’t a part of a whole day. They’re “left over” from the Division as a "Remainder". That number will NEVER be greater than or equal to the Divisor of 86400000, which is the number of milliseconds in a “WHOLE” day.
This “Remainder” is the “TIME” of day that the UNIX Timestamp represents.
A super easy way to get the “Remainder” is to do the same Integer Math Division that we did to get the number of “WHOLE” days but, instead of keeping that value (the Quotient), we just want to keep the “Remainder”.
There’s a Mathematical Operator for that. Instead of using “/” (which keeps only the Quotient in Integer Math), we can use the “Modulus” Operator (https://docs.microsoft.com/en-us/sql/t-sql/language-elements/modulo-transact-sql), which is “%”. It returns only the Remainder from Division and, since we’re using Integer Math, that Remainder will be a whole number that contains the number of “left over” milliseconds, which is the TIME of day.
Here’s the code to demonstrate that bit of simple math.
--===== Find the "left over" (Remainder) number of Milliseconds
-- in the previously identified UNIX Timestamps.
DECLARE @EarliestUnixTS BIGINT = -62135510400001
,@LatestUnixTS BIGINT = 253402300799999
;
SELECT EarliestTIME = @EarliestUnixTS % msPerDay
,LatestTIME = @LatestUnixTS % msPerDay
FROM (VALUES(86400000,CONVERT(DATETIME2(3),'1970')))v1(msPerDay,Epoch)
;
--Note that we will be using the "Epoch" part of the VALUES Table Valued Constructor later.And here are the results, which are the "Remaining" milliseconds that are not a part of the whole days.
Step 3: Add the “Time” (ms) to the Date
Time to put both the “Time” and the “Date” parts together. To summarize, we have a formula to extract and convert "Whole" days to "Whole" dates and we have a formula to isolate the left-over milliseconds, which is the "time of day". We just need to add those "time" milliseconds to our "Whole" date and we'll have the complete original Date/Times as a DATETIME2(3)
The Principle of the Code
Here’s the basic formula to extract the days from a “New” type of UNIX Timestamps and convert them to a date.
TheDate = DATEADD(dd,@AnyNewUnixTS/msPerDay,Epoch)
And here’s the formula to extract the time from a “New” type of UNIX Timestamp:
TheTime = @AnyNewUnixTS%msPerDay -- Expressed as milliseconds
All we have to do is add “TheTime” to “TheDate” and we’re done.
TheDateTime = DATEADD(ms,@AnyNewUnixTS%msPerDay,TheDate)
Making all the necessary substitutions, we end up with the following final formula for converting “New” type UNIX Timestamps to a DATETIME2(3) date/time
|<---- The TIME ---->| |<------------- The DATE ------------->| DATEADD(ms,@AnyNewUnixTS%msPerDay,DATEADD(dd,@AnyNewUnixTS/msPerDay,Epoch))
Putting that back into our original test code, we end up with the following:
--===== Convert the UNIX TImestamps DATETIME2(3)
-- date to convert them to a DATETIME2(3).
DECLARE @EarliestUnixTS BIGINT = -62135510400001
,@LatestUnixTS BIGINT = 253402300799999
; -- |<----- The TIME ----->| |<-------------- The DATE -------------->|
SELECT EarliestDATETIME2 = DATEADD(ms,@EarliestUnixTS%msPerDay,DATEADD(dd,@EarliestUnixTS/msPerDay,Epoch))
,LatestDATETIME2 = DATEADD(ms,@LatestUnixTS %msPerDay,DATEADD(dd,@LatestUnixTS /msPerDay,Epoch))
FROM (VALUES(86400000,CONVERT(DATETIME2(3),'1970')))v1(msPerDay,Epoch) --"DRY" values
;… and THAT gives us our original dates back proving that it all worked. It’s also quite fast because it’s all Integer and Date Math with only one occurrence of a string date of ‘1970’.
Let’s Make it Simple AND Easy – Convert to a Function
I'm a "lazy" DBA... I like "Simple AND Easy". 😀
Discussion on User Functions:
Converting the code that we ended up with to a User Defined Scalar Function would be a travesty for a bazillion reasons that all end up with a combination of poor performance and excessive CPU usage, not to mention that any code that uses the function would go "single threaded". You can find out more on all those subjects by doing the following search and reading the collection of Brent Ozar’s articles about such issues and some of the fixes. And, make sure you read the ones (there’s more than one) about using scalar functions in computed columns (they affect every query, not just the ones that refer to the computed column) and the fix (using triggers, which is actually a whole lot better!).
https://www.google.com/search?q=Brent+Ozar+Scalar+Functions
Instead, we’re going to convert this to a high-performance iTVF (inline Table Valued Function). You can learn how and why to use iTVFs at the following article.
https://www.sqlservercentral.com/articles/creating-and-using-inline-table-valued-functions
The Function Code
I try to name functions after what they do. You can pick any name that you like, but I’m calling this one “msUnixTStoDATETIME2”. The “ms” lets me know it’s for use with the “New” UNIX Timestamps that are based on milliseconds. I also know that, by definition, they will be of the BIGINT datatype.
I’ll say it right up front… DO NOT USE THIS FUNCTION TO CONVERT TO THE DATETIME DATATYPE!!! There will be rounding errors that are sometimes as horrendous as the bad way to do this as demonstrated earlier in the article.
And, as usual, the flower box that explains the goodies is MUCH larger than the code itself, because “Explaining simplicity is sometimes a complex task”. 😀
CREATE OR ALTER FUNCTION dbo.msUnixTStoDATETIME2
/****************************************************************************************
Purpose: Given a "New" type of BIGINT UNIX Timestamp based on milliseconds, convert it
to a DATETIME2(3).
***** WARNING ***** WARNING ***** WARNING ***** WARNING ***** WARNING ***** WARNING *****
***** DO NOT USE THIS FUNCTION TO CONVERT TO THE DATETIME DATATYPE!!! *****
***** There will be horrendous rounding errors because of DATETIME rounding. *****
***** WARNING ***** WARNING ***** WARNING ***** WARNING ***** WARNING ***** WARNING *****
-----------------------------------------------------------------------------------------
Usage Examples:
--===== Basic Syntax
SELECT TheDateTime
FROM dbo.msUnixTStoDATETIME2(@msUnixTS)
;
--===== Convert a single hard-coded millisecond based UNIX Timetimestamp
-- Converts to '2022-07-19 23:58:37.096'
SELECT TheDateTime
FROM dbo.msUnixTStoDATETIME2(1658275117096)
;
--===== Convert a column of millisecond based UNIX Timetimestamp to DATETIME2(3)
SELECT dt.TheDateTime
FROM dbo.SomeTable st
CROSS APPLY dbo.msUnixTStoDATETIME2(st.msUnixTsColumn) dt
;
-----------------------------------------------------------------------------------------
Performance: (on Alienware R 17 NVME SSD 32 GB RAM - SQL Server 2017 Developers Edition
Test output dumped to throw away variable from a TempTable to remove display times.
1,000 rows - CPU time = 0 ms, elapsed time = 0 ms.
10,000 rows - CPU time = 0 ms, elapsed time = 3 ms.
100,000 rows - CPU time = 31 ms, elapsed time = 33 ms.
1,000,000 rows - CPU time = 328 ms, elapsed time = 329 ms.
10,000,000 rows - CPU time = 3,250 ms, elapsed time = 3,257 ms.
100,000,000 rows - CPU time = 32,391 ms, elapsed time = 32,428 ms.
1,000,000,000 rows - CPU time = 319,218 ms, elapsed time = 325,037 ms.
-----------------------------------------------------------------------------------------
Ref Article: Convert UNIX Timestamps to DATETIMEs in SQL Server #1 by Jeff Moden
SITE: SQLServerCentral.com
-----------------------------------------------------------------------------------------
Revision History:
Rev 00 - 09 Apr 2022 - Jeff Moden
- Proof of principle, unit testing, and article work.
Rev 01 - 19 Jul 2022 - Jeff Moden
- Final documentation for the article.
****************************************************************************************/--
(@msUnixTS BIGINT)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN -- |<-- The TIME -->| |<----------- The DATE ----------->|
SELECT TheDateTime = DATEADD(ms,@msUnixTS%msPerDay,DATEADD(dd,@msUnixTS/msPerDay,Epoch))
FROM (VALUES(86400000,CONVERT(DATETIME2(3),'1970')))v1(msPerDay,Epoch) --"DRY" Parts
;
What About the Functions to Convert Date/Times to Unix Timestamps???
Heh... They're simple to make from the code near the beginning of the article. You have to have some of the "fun", especially if you got here from the "tl;dr" part. 😀
Epilogue
Like I said, it's a pretty long article to cover a "simple" task but, again, “Explaining simplicity is sometimes a complex task”.
We covered what UNIX Timestamps are, what some of the problems with the "Old" INTeger type are, why they needed to shift to a "New" BIGINT type, and how they took advantage of the massive scale of the BIGINT datatype to include milliseconds as a commonly used timestamp.
We learned what an Epoch was and found out that the Epoch Date for both "Old" and "New" UNIX Timestamps is the first instant of 01 January 1970.
We learned how to abbreviate dates for the first of the year as a 4 digit string representation of the year number.
Then we started work on code where we learned how simple it is to convert Date/Times to both the "Old" type of Unix Timestamps that are based on seconds and the "New" type based on milliseconds. We also saw how easy it is to convert the "Old" type back to Date/Times and why the Integer limits of DATEADD make such ease of conversion impossible for the "New" type.
After that, we took a deep breath and dug into the INTEGER MATH required to quickly and accurately convert the "New" type of UNIX Timestamps back to Date/Times (DATETIME2(3) to be specific) using simple but powerful "Integer Math". Finally, we ended up with a function that has only two short lines of active code that make the conversion of the "New" type of UNIX Timestamps to DATETIME2(3) datatypes quick and easy.
By example earlier in the article, we had a bit of an introduction to the problem that conversions from the "New" UNIX Timestamps to the DATETIME datatype challenge us with, and the reason why it's a problem (unwanted/improper rounding). Those problems and the solution will be covered in Part #2.
Thanks for listening, folks.
-- Jeff Moden
© Copyright - Jeff Moden, 22 July 2022, All Rights Reserved