How to Convert Epoch Timestamps to Human-Readable Dates and Times in SQL Server?

Convert epoch timestamp to human-readable date and time format in SQL Server

Unix timestamp represents the number of seconds elapsed since the beginning of the Unix epoch on January 1, 1970. When data containing epoch timestamps is imported into SQL Server, it needs to be converted into an easy-to-read format for analysis and display.

SQL Server provides the DATEADD function to handle dates and times to solve this conversion problem. By passing the epoch timestamp as the second parameter, and the reference date '19700101' (January 1, 1970) as the third parameter, you can calculate the corresponding SQL Server timestamp:

<code class="language-sql">SELECT DATEADD(ss, 1291388960, '19700101')</code>

This will return a DATETIME value that you can store in a table or use for further processing.

If you need the converted timestamp formatted in a specific way, you can use the CAST or CONVERT functions. For example, to format a timestamp as "2010-12-03 15:09:20.000":

<code class="language-sql">SELECT CAST(DATEADD(ss, 1291388960, '19700101') AS DATETIME2(0))</code>

Similarly, you can convert multiple columns containing epoch timestamps using the same method:

<code class="language-sql">UPDATE table_name
SET column1 = DATEADD(ss, column1, '19700101'),
    column2 = DATEADD(ss, column2, '19700101'),
    column3 = DATEADD(ss, column3, '19700101')
WHERE ...</code>

The above is the detailed content of How to Convert Epoch Timestamps to Human-Readable Dates and Times in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!