Convert Unix TimeStamp to DateTime
I am trying to select the largest date in my table, however the dates are stored in Unix TimeStamp format in an integer type column. I need to do this conversion because I need to calculate the difference between dates in seconds.
Date example 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 for the type of datetime data.
What am I doing wrong ? How to fix ?
1 answers
Just use the value you have, in seconds, added to 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:
Unix timestamp is a way to track time as a total of running seconds. This count begins on Unix Epoch on January 1, 1970, in UTC. Therefore, the unix timestamp is merely the number of seconds between a specific date and the Unix Epoch. It should also be stressed (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 client side.