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
– CmdrMoozy, Commented May 31, 2013 at 19:15
Oh, and you might try looking at this question - it's more or less the same thing that you're asking: stackoverflow.com/questions/1964544/… — CmdrMoozy
– CmdrMoozy, Commented May 31, 2013 at 19:21
As a novice the first thing to do is update to a supported, current version of PostgreSQL. — Craig Ringer
– Craig Ringer, Commented Jun 1, 2013 at 3:42

CmdrMoozy · Accepted Answer · 2013-05-31 19:18:08Z

4

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.

edited May 31, 2013 at 19:18

answered May 31, 2013 at 19:11

CmdrMoozy

3,9513 gold badges22 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgresql: generate interval count of hours between two timestamps

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related