time difference in minutes

General

time difference in minutes

HI,

I want to subtract 2 dates with timestamp and get the result like below

06/16/2014 23:33:52

06/16/2014 22:36:22

57.50

I tried the below statement but it gives 58 instead of 57.50

CAST((STG. READTIME - STG.GAS_HRLY_START_DTTM MINUTE(4))  AS INTEGER) AS TIME_ELAP_IN_MINS

How do i get the time difference like 57.50

1 REPLY
N/A

Re: time difference in minutes

this should do 

select cast('2014-06-16 23:33:52' as timestamp(0)) a,
cast('2014-06-16 22:36:22' as timestamp(0)) b,
a-b Minute(4) to Second c,
extract(minute from c) + extract(second from c) * 1.00 / 60
;

you can define a SQL UDF to keep the SQL cleaner