How to Convert Bigint UNIX Timestamps to DateTime in SQL Server?

SQL Server: Converting Bigint UNIX Timestamps to DateTime

This guide demonstrates how to efficiently convert bigint UNIX timestamps into DateTime values within SQL Server.

The primary method involves using the DATEADD function:

<code class="language-sql">SELECT DATEADD(SECOND, [unixtime], '19700101')
FROM [Table]</code>

This query adds the number of seconds represented by the unixtime column (since the Unix epoch) to the base date of January 1st, 1970, yielding the corresponding DateTime.

Understanding the Epoch

The date '19700101' signifies the Unix epoch—the starting point for UNIX timestamps (January 1, 1970, 00:00:00 UTC).

Addressing the Year 2038 Problem: A Robust Solution

Standard 32-bit integer UNIX timestamps have a limitation: they overflow on January 19, 2038, at 03:14:07 UTC. To handle timestamps beyond this date, we can employ a two-step DATEADD approach:

<code class="language-sql">DECLARE @t BIGINT = 4147483645;
DECLARE @oneyear INT = 31622400; -- Approximate seconds in a year

SELECT (@t / @oneyear) -- Years to add
SELECT (@t % @oneyear) -- Remaining seconds

-- DateTime calculation for timestamp @t
SELECT DATEADD(SECOND, @t % @oneyear, DATEADD(YEAR, @t / @oneyear, '19700101'));</code>

This method first calculates the number of years and remaining seconds, then applies them sequentially to the epoch date, preventing overflow errors and ensuring accurate conversions for timestamps beyond 2038.

The above is the detailed content of How to Convert Bigint UNIX Timestamps to DateTime in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!