Unix timestamps (also known as epoch time) are a simple way of representing a point in time: the number of seconds that have passed since 00:00:00 UTC on January 1, 1970 UTC. They’re popular in APIs, logs, and systems that need a compact, language-neutral way to store time.
If you’re working with SQL Server, you’ll almost certainly run into Unix timestamps eventually. Either you’re getting them from an external system or you need to produce them for one. Let’s walk through how to handle them in SQL Server.
1. Converting a Unix Timestamp to a DateTime in SQL Server
SQL Server doesn’t have a built-in FROM_UNIXTIME() function like MySQL, but it does have a DATEADD() function that can help us calculate the result. The math is straightforward:
- Start from
'1970-01-01'(the epoch) - Add the Unix timestamp value as seconds
- Use
DATEADD()to do the calculation
Here’s an example that demonstrates how it works:
SELECT DATEADD(SECOND, 1691760000, '1970-01-01');Output:
2023-08-11 13:20:00.000
Here, we convert a Unix timestamp of 1691760000 to a datetime value. Basically, all we did was add 1,691,760,000 seconds to 1970-01-01. When we omit the time part from 1970-01-01, it assumes 00:00:00.
When the Unix Timestamp is in Milliseconds
If you’re working with fractional seconds (millisecond or microsecond resolution), you might get a Unix timestamp in milliseconds instead of seconds. You can use the following approach to deal with this:
DECLARE @UnixMilli BIGINT = 1691760000123;
SELECT DATEADD(MILLISECOND, @UnixMilli % 1000,
DATEADD(SECOND, @UnixMilli / 1000, '1970-01-01'));Result:
2023-08-11 13:20:00.123
If you’re concerned about whether or not SQL Server will return a datetime2 value (and therefore provide the full precision), you could cast it as datetime2:
DECLARE @UnixMilli BIGINT = 1691760000123;
SELECT CAST(
DATEADD(MILLISECOND, @UnixMilli % 1000,
DATEADD(SECOND, @UnixMilli / 1000, '1970-01-01')
) AS DATETIME2(3));Result:
2023-08-11 13:20:00.123
Another way to do it is like this:
DECLARE @UnixMilli BIGINT = 1691760000123;
SELECT DATEADD(MILLISECOND, @UnixMilli % 1000,
DATEADD(SECOND, @UnixMilli / 1000, CAST('1970-01-01' AS DATETIME2(3))));Result:
2023-08-11 13:20:00.123
2. Converting a Date/Time to a Unix Timestamp
Going the other way is just as simple. You can convert a date/time value to a Unix timestamp by subtracting the epoch date and getting the difference in seconds:
DECLARE @MyDate DATETIME = '2023-08-11 00:00:00';
SELECT DATEDIFF(SECOND, '1970-01-01', @MyDate);Output:
1691712000
Getting Millisecond Unix Timestamps
For systems that use milliseconds:
DECLARE @MyDate DATETIME2 = '2023-08-11 00:00:00.123';
SELECT DATEDIFF_BIG(MILLISECOND, '1970-01-01', @MyDate);Output:
1691712000123
Here we use DATEDIFF_BIG() to handle large values safely.
3. The Y2038 Problem
On problem with the classic Unix timestamp is the Y2038 problem. The Unix timestamp has traditionally been stored as a signed 32-bit integer. This causes an issue when using dates past the year 2038 (2038-01-19 03:14:07 UTC to be precise). That’s because the signed 32-bit integer has an upper limit of 2,147,483,647. It can’t go past that. If we try to go past that, even for a single second, we’ll get an error:
DECLARE @UnixTs INT = 2147483648;
SELECT DATEADD(SECOND, @UnixTs, '1970-01-01');Output:
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type int.
To overcome this issue, we can store our Unix timestamps as a bigint. This enables us to go well past the year 2038. When we do our calculations, we’ll need to do a bit of extra work. For example:
DECLARE @UnixTs BIGINT = 2147483648;
-- Break down the timestamp into days and remaining seconds
DECLARE @Days INT = @UnixTs / 86400;
DECLARE @RemainingSeconds BIGINT = @UnixTs % 86400;
-- Add days first, then add the remaining seconds
SELECT DATEADD(SECOND, @RemainingSeconds, DATEADD(DAY, @Days, '1970-01-01'));Output:
2038-01-19 03:14:08.000
Here, we split the large bigint value into days and remaining seconds, which DATEADD() can handle. In this case we used DATEADD() with a different date part (in this case DAY), and then we calculated the remainder with the SECOND date part.
And going the other way we can use DATEDIFF_BIG():
DECLARE @FutureDate DATETIME2 = '2038-01-19 03:14:08.000';
DECLARE @Epoch DATETIME2 = '1970-01-01 00:00:00.000';
SELECT DATEDIFF_BIG(SECOND, @Epoch, @FutureDate);Result:
2147483648
4. Handy Reusable Functions
If you do this often, creating helper functions will save you time.
For example:
-- Unix seconds to DATETIME2
CREATE FUNCTION dbo.UnixToDateTime(@UnixSeconds BIGINT)
RETURNS DATETIME2 AS
BEGIN
RETURN DATEADD(SECOND, @UnixSeconds, '1970-01-01');
END
GO
-- DATETIME2 to Unix seconds
CREATE FUNCTION dbo.DateTimeToUnix(@DateTime DATETIME2)
RETURNS BIGINT AS
BEGIN
RETURN DATEDIFF(SECOND, '1970-01-01', @DateTime);
END
GO
Then you can do something like this:
SELECT dbo.UnixToDateTime(1691760000), dbo.DateTimeToUnix(SYSDATETIME());Output:
UnixToDateTime DateTimeToUnix
--------------------------- --------------
2023-08-11 13:20:00.0000000 1755226366