Solved

# pgsql efficient time lapse calculation in hours

Posted on 2014-07-15
296 Views
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
Question by:dearnemo

LVL 22

Expert Comment

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 Comment

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

LVL 22

Expert Comment

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 Comment

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

LVL 22

Accepted Solution

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

### Suggested Solutions

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.