Have you ever come across a UNIX Timestamp and wondered how to convert it to a human-readable date and time in SQL Server? If so, you’re not alone. Many SQL Server professionals have encountered this challenge and have found various solutions. However, not all of these solutions account for certain issues, such as the “2038/Friday the 13th” problem and negative UNIX Timestamps.

In this article, we’ll explore what UNIX Timestamps are, the different types of UNIX Timestamps, and how to convert them to the DATETIME datatype in SQL Server while avoiding common pitfalls.

What is a UNIX Timestamp?

A UNIX Timestamp is a whole number that represents a date and time. It is based on the “Epoch,” which is the first instant of January 1, 1970, in the Gregorian Calendar. Positive UNIX Timestamps represent dates and times after the Epoch, while negative UNIX Timestamps represent dates and times before the Epoch.

There are two common types of UNIX Timestamps:

• The “Old” type, which is based on the number of seconds before or after the Epoch. It is usually represented as a 4-byte INTeger.
• The “New” type, which is based on the number of milliseconds before or after the Epoch. It is usually represented as an 8-byte BIGINT.

It’s important to note that there are variations for microseconds and nanoseconds, but we won’t cover those in this article.

The “2038/Friday the 13th” Problem

The “Old” type of UNIX Timestamp, based on seconds, has a limitation known as the “2038 Problem.” This problem arises because the maximum value of a 4-byte INTeger is exceeded just one second after the date/time of “2038-01-19 03:14:07.” This limitation is also known as the “Y2K38” problem or the “Friday the 13th” problem because the representative date occurs on a “Friday the 13th.”

To overcome this limitation, the “New” type of UNIX Timestamp was introduced, which uses an 8-byte BIGINT to represent the number of milliseconds before or after the Epoch. This allows for a much larger range of dates and times, but it introduces new challenges when converting them to the DATETIME datatype in SQL Server.

Converting UNIX Timestamps to DATETIME

Converting a UNIX Timestamp to the DATETIME datatype in SQL Server is relatively straightforward. For both the “Old” and “New” types of UNIX Timestamps, you can calculate the difference between the UNIX Epoch date (January 1, 1970) and the date/time you want to convert.

Here’s an example of converting a given date/time to both the “Old” and “New” UNIX Timestamps:

-- 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.

Once you have the UNIX Timestamp, you can convert it back to a date/time using the DATEADD function. However, there are some challenges when converting the “New” type of UNIX Timestamps due to the limitations of DATEADD with BIGINT values.

Converting “New” Type UNIX Timestamps to DATETIME2(3)

Converting the “New” type of UNIX Timestamps, based on milliseconds, to the DATETIME2(3) datatype in SQL Server requires a different approach. The DATEADD function cannot handle BIGINT values, and there is no DATEADD_BIG function available.

Here’s a step-by-step process to convert “New” type UNIX Timestamps to DATETIME2(3) in SQL Server:

1. Calculate the number of milliseconds in a day using DATEDIFF.
2. Calculate the number of “whole” days between the UNIX Epoch and the given UNIX Timestamp using Integer Math Division.
3. Calculate the remaining milliseconds (the “time” part) using the Modulus Operator.
4. Add the remaining milliseconds to the “whole” days to get the complete DATETIME2(3) value.

Here’s an example of converting a “New” type UNIX Timestamp to DATETIME2(3) using the above steps:

-- Convert the UNIX Timestamp to DATETIME2(3)
DECLARE @UnixTimestamp BIGINT = 1658275117096;

SELECT TheDateTime = DATEADD(ms, @UnixTimestamp % msPerDay, DATEADD(dd, @UnixTimestamp / msPerDay, Epoch))
FROM (VALUES (86400000, CONVERT(DATETIME2(3), '1970'))) v1(msPerDay, Epoch);

By following these steps, you can accurately convert “New” type UNIX Timestamps to the DATETIME2(3) datatype in SQL Server while avoiding rounding errors and other issues.

Conclusion

Understanding UNIX Timestamps and converting them to human-readable date and time values in SQL Server can be a complex task. However, by following the steps outlined in this article, you can overcome common challenges and accurately convert UNIX Timestamps to the DATETIME2(3) datatype.

Remember to consider the limitations of the “Old” and “New” types of UNIX Timestamps and choose the appropriate conversion method based on your requirements. Additionally, be aware of the potential rounding errors when converting “New” type UNIX Timestamps to the DATETIME datatype.

By mastering the conversion process, you’ll be able to work with UNIX Timestamps effectively in SQL Server and handle various date and time scenarios with confidence.

Thank you for reading!