You might have noticed that SQL Server doesn’t have an equivalent of MySQL‘s UNIX_TIMESTAMP() function.

However, it’s not that difficult to return the Unix timestamp in SQL Server.

The Unix timestamp (also known as Unix Epoch time, Unix time, or POSIX time) is simply the number of seconds that have elapsed since 00:00:00 Thursday, 1 January 1970, Coordinated Universal Time (UTC). Therefore, in SQL Server we can use a couple of T-SQL functions to return this.

SQL Server Unix Timestamp

Here’s how you can return the Unix timestamp in SQL Server.

SELECT DATEDIFF(SECOND,'1970-01-01', GETUTCDATE()) AS 'SQL Server Result';

Result:

+---------------------+
| SQL Server Result   |
|---------------------|
| 1560833178          |
+---------------------+

So we can use the DATEDIFF() function to return the difference in seconds between 1970-01-01 and now. We use the GETUTCDATE() function to return the current date and time in UTC time.

This code will work up until the year 2038 (‘2038-01-19 03:14:07’ to be precise). For Unix timestamps after that, you’ll need to modify the code slightly. If you need a Unix timestamp past that date, read on.

MySQL Unix Timestamp Equivalent

For a comparison, if I run MySQL’s UNIX_TIMESTAMP() at exactly the same time, I get this:

SELECT UNIX_TIMESTAMP() AS 'MySQL Result';

Result:

+--------------+
| MySQL Result |
+--------------+
|   1560833178 |
+--------------+

Same result. MySQL returns the result as an unsigned integer. However, if the (optional) date argument is passed, it supports the same range as the TIMESTAMP data type.

Return the Milliseconds

If you need to return a Unix timestamp with higher precision, say, the number of milliseconds since ‘1970-01-01 00:00:00.000’ UTC, you’ll need to swap the DATEDIFF() function for DATEDIFF_BIG().

This is because DATEDIFF() returns an int, which is too small to handle the number of milliseconds since 1970. The DATEDIFF_BIG() function on the other hand, returns a signed bigint, which is more than enough to handle milliseconds.

SELECT DATEDIFF_BIG(MILLISECOND,'1970-01-01 00:00:00.000', SYSUTCDATETIME()) Milliseconds;

Result:

+----------------+
| Milliseconds   |
|----------------|
| 1560835305461  |
+----------------+

Return the Nanoseconds

Here’s another example, this time going all the way up to the nanoseconds since ‘1970-01-01 00:00:00.0000000’ UTC.

SELECT DATEDIFF_BIG(NANOSECOND,'1970-01-01 00:00:00.0000000', SYSUTCDATETIME()) Nanoseconds;

Result:

+---------------------+
| Nanoseconds         |
|---------------------|
| 1560835321500279300 |
+---------------------+

The Year 2038 Problem

Returning milliseconds, microseconds, and nanoseconds is all good and well, but strictly speaking, it’s not true Unix Epoch time. Unix Epoch time is the number of seconds since ‘1970-01-01 00:00:00’.

However, the DATEDIFF_BIG() can still come in handy when returning strict Unix Epoch time. In particular, it could help your database overcome the 2038 problem. In this case, anything after ‘2038-01-19 03:14:07’ will need to be returned as a bigint (an 8 byte integer). This is because the number of seconds will be too big for an int data type (a 4 byte integer). The int data type only goes up to 2,147,483,647, whereas a bigint goes up to 9,223,372,036,854,775,807.

Therefore, to return Unix timestamps past ‘2038-01-19 03:14:07’, use the DATEDIFF_BIG() function instead of DATEDIFF().

To demonstrate this, here’s an example of using DATEDIFF() to return Unix time at exactly that date/time:

SELECT DATEDIFF(SECOND,'1970-01-01', '2038-01-19 03:14:07') AS 'Unix Epoch time';

Result:

+-------------------+
| Unix Epoch time   |
|-------------------|
| 2147483647        |
+-------------------+

So far, so good. This result is within the int range of -2,147,483,648 to 2,147,483,647 (although it’s right at the upper limit) so the correct result is returned.

But here’s what happens if we increase it by one second:

SELECT DATEDIFF(SECOND,'1970-01-01', '2038-01-19 03:14:08') AS 'Unix Epoch time';

Result:

The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

We get the error because the returned value would be outside the int range.

So as mentioned, all we need to do is swap DATEDIFF() for DATEDIFF_BIG():

SELECT DATEDIFF_BIG(SECOND,'1970-01-01', '2038-01-19 03:14:08') AS 'Unix Epoch time';

Result:

+-------------------+
| Unix Epoch time   |
|-------------------|
| 2147483648        |
+-------------------+

However, this won’t necessarily solve all year 2038 problems, as most potential issues would probably be derived from the operating system.

I should also point out that none of this code will not necessarily be immune to the “Year 10,000 Problem”, because it involves the datetime2 data type, which has an upper range of ‘9999-12-31’.