Convert Unix Timestamp to Datetime

Asked

Viewed 1,240 times

4

I am trying to select the largest date from my table, however the dates are stored in Unix Timestamp format in a whole column type. I need to do this conversion because I need to calculate the difference between dates in seconds.

Example of Date and Unix Timestamp respectively: 2019-08-02 15:00:35 = 1564758035

I’m trying this form :

DECLARE @LastValue DATETIME = (SELECT CONVERT (DATETIME, MAX(Column_Name))
                               FROM Table_Name
                               WHERE Condição)

Error :

Arithmetic overflow error converting the expression to the type of datetime data.

What am I doing wrong? How to solve ?

sql database sql-server type-conversion

  • You have the example of a data in the table and its respective date?

  • 1

    @Sorack I just edited the question providing an example.

  • 1

    If your dates are stored as UNIX Epoch time, that is: the number of seconds elapsed from the zero hour of 01/01/1970, then just take the highest value and to calculate the difference in seconds just subtract the two dates, no conversion is required for this calculation.

1 answer

3


Just use the value you have, in seconds, added to day 01/01/1970:

SELECT DATEADD(SECOND, Column_Name, CAST('1970-01-01 00:00:00' AS DATETIME))
  FROM Table_Name
 WHERE Condição

What is the Unix time Stamp?

The Unix time Stamp is a way to track time as a running total of Seconds. This Count Starts at the Unix Epoch on January 1st, 1970 at UTC. Therefore, the Unix time Stamp is merely the number of Seconds between a particular date and the Unix Epoch. It should also be pointed out (Thanks to the comments from Visitors to this site) that this point in time technically does not change no Matter Where you are Located on the Globe. This is very Useful to computer systems for tracking and Sorting dated information in Dynamic and Distributed Applications Both online and client side.

In free translation:

The Unix date and time record is a way to track time as a total of running seconds. This count begins at the Unix Epoch on January 1, 1970, in UTC. Therefore, Unix’s date and time record is merely the number of seconds between a specific date and the Unix Epoch. It should also be noted (thanks to the comments of visitors to this site) that this point in time technically does not change, no matter where you are located on the globe. This is very useful for computer systems for tracking and sorting dated information in dynamic and distributed applications both online and on the customer side.

Browser other questions tagged sql database sql-server type-conversion

You are not signed in. Login or sign up in order to post.