Convert time interval to units of hours

LabKey Support Forum (Inactive)
Convert time interval to units of hours Will Holtz  2015-12-14 15:46
Status: Closed
 
Hi Matthew,

Yes, using timestampdiff with 'SQL_TSI_MINUTE' should do what I need. But something is broken with timestampdiff for me. Here is another test I did:
SELECT
to_timestamp('05 Dec 2000 02:33', 'DD Mon YYYY HH24:MI') - to_timestamp('05 Dec 2000 01:13', 'DD Mon YYYY HH24:MI') AS interval01,
to_timestamp('08 Dec 2000 02:33', 'DD Mon YYYY HH24:MI') - to_timestamp('05 Dec 2000 01:13', 'DD Mon YYYY HH24:MI') AS interval02,
to_timestamp('08 Dec 2004 03:12', 'DD Mon YYYY HH24:MI') - to_timestamp('05 Dec 2000 01:13', 'DD Mon YYYY HH24:MI') AS interval03,
timestampdiff('SQL_TSI_MINUTE', to_timestamp('05 Dec 2000 01:13', 'DD Mon YYYY HH24:MI'), to_timestamp('05 Dec 2000 02:33', 'DD Mon YYYY HH24:MI'))/60 AS tsdiff01,
timestampdiff('SQL_TSI_MINUTE', to_timestamp('05 Dec 2000 01:13', 'DD Mon YYYY HH24:MI'), to_timestamp('08 Dec 2000 02:33', 'DD Mon YYYY HH24:MI'))/60 AS tsdiff02,
timestampdiff('SQL_TSI_MINUTE', to_timestamp('05 Dec 2000 01:13', 'DD Mon YYYY HH24:MI'), to_timestamp('08 Dec 2004 03:12', 'DD Mon YYYY HH24:MI'))/60 AS tsdiff03
FROM core.users
LIMIT 1;

gives the output:
0 years 0 mons 0 days 1 hours 20 mins 0.00 secs    0 years 0 mons 3 days 1 hours 20 mins 0.00 secs    0 years 0 mons 1464 days 1 hours 59 mins 0.00 secs    0.3333333333333333    0.3333333333333333    0.9833333333333333

So for me, timestampdiff appears to be just extracting the value of the 'interval' portion of the time difference and not converting the total time difference into 'interval' units.

-Will