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.
Look at the above post.
I think you could use that to do the difference after conversion.
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
,TS2_Sec - TS1_Sec;