# 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?
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
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)
0
Author Commented:
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.
0
Commented:
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)
0
Author Commented:
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.
0
Commented:
Using extract epoch is the easiest way to get what you want see

http://www.postgresql.org/docs/9.2/static/functions-datetime.html#FUNCTIONS-DATETIME-EXTRACT

if you only want the magnitude of the timestamp difference then you use the ABS mathematical function.

Generally you want to keep intervals in their native form so you only convert to hours at the last step eg for hourly billing.
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PostgreSQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.