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 |
||