IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this forum will no longer be available. More details available on our FAQ.
Topic
  • 2 replies
  • Latest Post - ‏2019-10-11T07:47:19Z by mkoni
mkoni
mkoni
54 Posts

Pinned topic Calculate time difference in seconds

‏2019-10-10T09:20:20Z | calculate difference in seconds time

Hi,

I want to calculate a timedifference in seconds from two timestamps. I found different ways to do this.

but I get diferent result that I dont understand, maybe someone can explain me the difference? or did I made an error?

following example

SELECT START_TIME, END_TIME,
ROUND_TIMESTAMP(START_TIME, 'SS') AS START_TIME_ROUNDED,
ROUND_TIMESTAMP(END_TIME, 'SS') AS END_TIME_ROUNDED,
CAST((( TIMESTAMPDIFF ( 1 , CAST( ( TIMESTAMP ( END_TIME ) - TIMESTAMP ( START_TIME ) ) AS CHAR(22) ) ))) AS FLOAT) / 1000000 AS "END-START_V1",
CAST (ROUND(FLOAT ( TIMESTAMPDIFF ( 1 , CAST( ( TIMESTAMP ( END_TIME ) - TIMESTAMP ( START_TIME ) ) AS CHAR(22) ) )) / 1000000 , 0) AS INTEGER) AS "END-START_V2",
CAST((ROUND_TIMESTAMP(END_TIME, 'SS') - ROUND_TIMESTAMP(START_TIME, 'SS')) AS INTEGER) AS "END-START_V3"
FROM (VALUES('2019-10-09 00:22:22.208541', '2019-10-09 00:48:59.027025'), 
            ('2019-10-09 00:48:59.036479', '2019-10-09 00:49:25.075002'), 
            ('2019-10-09 00:49:25.079127', '2019-10-09 00:49:48.362228'), 
            ('2019-10-09 00:49:48.364414', '2019-10-09 00:50:15.773217'), 
            ('2019-10-09 00:50:15.776181', '2019-10-09 00:50:19.443214'), 
            ('2019-10-09 00:50:19.445993', '2019-10-09 00:50:39.885831'), 
            ('2019-10-09 00:50:39.890557', '2019-10-09 00:51:11.110084'), 
            ('2019-10-09 00:51:11.117728', '2019-10-09 00:53:20.824089'), 
            ('2019-10-09 00:53:20.838489', '2019-10-09 00:53:20.895874'), 
            ('2019-10-09 00:53:20.910607', '2019-10-09 00:53:21.610278'), 
            ('2019-10-09 00:53:21.621467', '2019-10-09 00:53:22.642263'), 
            ('2019-10-09 00:53:22.653991', '2019-10-09 00:53:40.519419'), 
            ('2019-10-09 00:53:40.527316', '2019-10-09 00:53:41.511653'), 
            ('2019-10-09 00:53:41.519191', '2019-10-09 00:55:14.579569'), 
            ('2019-10-09 00:55:14.593064', '2019-10-09 01:01:27.841281'), 
            ('2019-10-09 01:01:27.854738', '2019-10-09 01:08:05.713431'), 
            ('2019-10-09 01:08:05.720476', '2019-10-09 01:08:22.283330') 
     ) AS T(START_TIME, END_TIME)
;

brings the following result (the last two columns should have the same result +/- 1)

 

I found no reason why only some are different?

 

regards mike

  • B.Hauser
    B.Hauser
    320 Posts
    ACCEPTED ANSWER

    Re: Calculate time difference in seconds

    ‏2019-10-10T17:32:13Z  

    Works as designed!

    If 2 timestamps are subtracted directly, the result is a numeric value in the format YYYYMMDDHHMMSS,MSMSMS. So the first _V3 Result means 26 Minutes and 37 seconds which is the same as 1597 seconds

    Birgitta

  • B.Hauser
    B.Hauser
    320 Posts

    Re: Calculate time difference in seconds

    ‏2019-10-10T17:32:13Z  

    Works as designed!

    If 2 timestamps are subtracted directly, the result is a numeric value in the format YYYYMMDDHHMMSS,MSMSMS. So the first _V3 Result means 26 Minutes and 37 seconds which is the same as 1597 seconds

    Birgitta

  • mkoni
    mkoni
    54 Posts

    Re: Calculate time difference in seconds

    ‏2019-10-11T07:47:19Z  
    • B.Hauser
    • ‏2019-10-10T17:32:13Z

    Works as designed!

    If 2 timestamps are subtracted directly, the result is a numeric value in the format YYYYMMDDHHMMSS,MSMSMS. So the first _V3 Result means 26 Minutes and 37 seconds which is the same as 1597 seconds

    Birgitta

    sounds good,

    thank you