Difference in Timestamps in seconds

Analytics
Enthusiast

Difference in Timestamps in seconds

Can anyone provide me solution for timestamp difference in seconds.

 

t1 = 4/15/2017 17:34:52

t2 = 4/16/2017 18:43:54

t2 - t1 = 86400+3600+540+ 2 =90542 seconds

 

i know that if we subtract timestamps we will get 

('4/17/2017 10:43:17'- '4/15/2017 17:34:52') DAY(4) TO SECOND as diff the answer is    1 17:08:25.000000. But im looking for some solution that give me caluculated value in Seconds.

 

Thanks in advance.

1 REPLY
Enthusiast

Re: Difference in Timestamps in seconds

Timestamp to epoch conversion in teradata

 

Look at the above post.

I think you could use that to do the difference after conversion.

 

SELECT
CAST('2017-07-02 02:00:21' AS TIMESTAMP(0)) ts1
,CAST('2017-07-03 02:00:21' AS TIMESTAMP(0)) ts2

 

,(CAST(ts1 AS DATE) - DATE '1970-01-01') * 86400 + (EXTRACT(HOUR FROM ts1) * 3600) + (EXTRACT(MINUTE FROM ts1) * 60) + (EXTRACT(SECOND FROM ts1)) TS1_Sec

 

,(CAST(ts2 AS DATE) - DATE '1970-01-01') * 86400 + (EXTRACT(HOUR FROM ts2) * 3600) + (EXTRACT(MINUTE FROM ts2) * 60) + (EXTRACT(SECOND FROM ts2)) TS2_sec

 

,24*60*60 TotalSecInDay

,TS2_Sec - TS1_Sec;