[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • Last Modified:

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?
0
dearnemo
Asked:
dearnemo
  • 3
  • 2
1 Solution
 
earth man2Commented:
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
 
dearnemoAuthor 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
 
earth man2Commented:
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
 
dearnemoAuthor 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
 
earth man2Commented:
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now