postgresql: generate interval count of hours between two timestamps

Question

I am using postgres 8.3.12 and novice.

I have a column formatted as follows: '29/04/2013 at 09:27:51 PM (or AM)'

I am doing the following to set them to a timestamp:

case when <criteria> to_timestamp(time, 'MM/DD/YYYY at HH:MI:SS AM') as start 
case when <criteria> to_timestamp(time, 'MM/DD/YYYY at HH:MI:SS AM') as end

My goal is to calculate the hours between two time stamps.

I looked at http://www.postgresql.org/docs/current/static/functions-datetime.html

After I set them to timestamps, is it simply end - start as difference;

Yes - you really should be using *at least* 8.4.17, if not just upgrading all the way to 9.2.4. At least if you use 8.4.17 you'll be mostly free of security issues. — CmdrMoozy, May 31 '13 at 19:15
Oh, and you might try looking at this question - it's more or less the same thing that you're asking: http://stackoverflow.com/questions/1964544/timestamp-difference-in-hours-for-postgresql — CmdrMoozy, May 31 '13 at 19:21
As a novice the first thing to do is update to a supported, current version of PostgreSQL. — Craig Ringer, Jun 01 '13 at 03:42

CmdrMoozy · Accepted Answer · 2013-05-31T19:18:08.223

Assuming you have two columns named timea and timeb, the following will return you the number of hours between them in PostgreSQL:

SELECT EXTRACT(EPOCH FROM timea - timeb) / 3600 AS hours FROM ...;

It might also be useful to note that:

EXTRACT(EPOCH FROM timea - timeb)

Also, timea and timeb don't need to be columns - you can use whatever expressions you want here, as long as they are timestamps.

Will return to you the number of seconds between the two timestamps - this can be useful to compute the number of minutes, hours, days, etc. - whatever you like. Hours, in particular, contain 3600 seconds, so you simply divide by 3600 to get the number of hours.

The EXTRACT function can do all kinds of powerful things for you. I'd suggest looking at the documentation for it here.

postgresql: generate interval count of hours between two timestamps

1 Answers1