Complete Guide to UNIX Timestamp and DateTime Conversion in SQL Server

Fundamental Concepts of UNIX Timestamps

A UNIX timestamp is an integer value representing the number of seconds or milliseconds elapsed since January 1, 1970, 00:00:00 UTC (known as epoch time). When handling timestamp conversions in SQL Server environment, special attention must be paid to data type limitations and precision requirements.

Simple Conversion Method: Second-Based Timestamps

For standard INT type UNIX timestamps (based on seconds), direct conversion can be performed using the DATEADD function:

SELECT DATEADD(SECOND, [unixtime], '1970-01-01') AS ConvertedDateTime
FROM [Table]

This method is suitable for most common scenarios where the [unixtime] column contains UNIX timestamp values in seconds. The second parameter of the DATEADD function accepts INT type, adding the specified number of seconds to the epoch time baseline.

Year 2038 Problem and Solutions

Traditional INT type timestamps face the Year 2038 problem because the maximum INT value of 2147483647 corresponds to January 19, 2038, 03:14:07. Exceeding this timestamp causes arithmetic overflow errors. To address this issue, modern systems typically use BIGINT type to store millisecond-level timestamps.

Advanced Methods for BIGINT Timestamp Processing

For BIGINT type millisecond timestamps, a stepwise processing strategy is required:

DECLARE @t AS BIGINT = 4147483645
DECLARE @seconds_per_year AS INT = 31622400

SELECT DATEADD(SECOND, @t % @seconds_per_year, 
               DATEADD(YEAR, @t / @seconds_per_year, '1970-01-01')) AS ConvertedDateTime

This approach avoids integer overflow in single DATEADD calls by decomposing the timestamp into year components and remaining seconds.

Precise Conversion for Millisecond Timestamps

For more accurate millisecond timestamp conversion, a day-based calculation method is necessary:

DECLARE @ms_unix_ts BIGINT = 1658275117096
DECLARE @ms_per_day INT = 86400000

SELECT DATEADD(MILLISECOND, @ms_unix_ts % @ms_per_day,
               DATEADD(DAY, @ms_unix_ts / @ms_per_day, '1970-01-01')) AS PreciseDateTime

This method first calculates the complete days portion, then processes the remaining milliseconds, ensuring conversion precision.

High-Performance Conversion Function Implementation

For reusability and performance improvement, an inline table-valued function can be created:

CREATE FUNCTION dbo.MsUnixTsToDateTime2(@ms_unix_ts BIGINT)
RETURNS TABLE
AS
RETURN
SELECT DATEADD(MILLISECOND, @ms_unix_ts % 86400000,
               DATEADD(DAY, @ms_unix_ts / 86400000, '1970-01-01')) AS ConvertedDateTime

This function accepts BIGINT millisecond timestamps and returns DATETIME2 datetime values, avoiding performance issues associated with scalar functions.

Handling Negative Timestamps

UNIX timestamps support negative values representing dates before the epoch. When converting negative timestamps, the DATEADD function automatically handles date calculations:

SELECT DATEADD(SECOND, -3600, '1970-01-01') AS BeforeEpoch

This returns December 31, 1969, 23:00:00, correctly processing time points before the epoch.

Data Type Selection Recommendations

When choosing target data types, DATETIME2 is recommended over DATETIME:

• DATETIME2 supports wider date range (0001-01-01 to 9999-12-31)
• DATETIME2 provides higher precision (up to 100 nanoseconds)
• DATETIME2 avoids rounding issues present in DATETIME

Practical Application Examples

Application in actual database queries:

-- Convert timestamp columns in tables
SELECT UserID,
       (SELECT ConvertedDateTime 
        FROM dbo.MsUnixTsToDateTime2(UnixTimestamp)) AS ActivityTime
FROM UserActivities
WHERE UnixTimestamp IS NOT NULL

Performance Optimization Considerations

When processing large volumes of timestamp conversions, it is recommended to:

• Use inline table-valued functions instead of scalar functions
• Use persisted computed columns where possible
• Avoid direct use of conversion functions in WHERE clauses
• Consider using triggers to maintain converted datetime columns

Error Handling and Edge Cases

Edge cases to consider in practical applications:

-- Handle NULL values
SELECT COALESCE(
    (SELECT ConvertedDateTime FROM dbo.MsUnixTsToDateTime2(UnixTimestamp)),
    '1900-01-01'
) AS SafeDateTime
FROM LogTable

Through the methods introduced in this article, various scenarios of UNIX timestamp conversion in SQL Server can be comprehensively addressed, from simple second-based conversions to complex millisecond-level precise conversions, ensuring data accuracy and system stability.