asked on

pgsql efficient time lapse calculation in hours

Hi

What is the best efficient way to calculate the time lapse in hours between two 'timestamp without time zone' fields in pgsql table such as time lapse in hours between 2012-05-31 15:55:07 and 2012-05-31 18:25:07?

earth man2

select extract( hour from (cast('2012-05-31 18:25:07' as timestamp) - cast('2012-05-31 15:55:07' as timestamp ))) as hours;

hours
-------
2
(1 row)

dearnemo

ASKER

Thanks earthman2 but here's the issue-
select extract( hour from (cast('2012-05-08 18:07:33' as timestamp) - cast('2012-05-08 18:08:04' as timestamp ))) as hours;
gives me 0. I want the difference in hours. Even if its a difference in minutes or seconds, that should be converted to hours.

earth man2

epoch is the number of seconds since start of 1970 AD. When applied to a duration it extracts seconds. All you need then is to know how many seconds in an hour. eg

select extract( epoch from (cast('2012-05-31 18:25:07' as timestamp) - cast('2012-05-31 15:55:07' as timestamp )))/3600.
0 as hours;
hours
-------
2.5
(1 row)

select extract( epoch from (cast('2012-05-08 18:07:33' as timestamp) - cast('2012-05-08 18:08:04' as timestamp )))/3600.
0 as hours;
hours
----------------------
-0.00861111111111111
(1 row)

dearnemo

ASKER

Ty again. Could you please help me understand why did we use epoch here? If I m going to plot a chart against this value, should it be -ve? TIA.

ASKER CERTIFIED SOLUTION

earth man2

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial