CODEWITHSUNDEEP
postgresql
lostinthebits
lostinthebits

Reputation: 661

postgresql: generate interval count of hours between two timestamps

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;

Upvotes: 2

Views: 4862

Answers (1)

CmdrMoozy
CmdrMoozy

Reputation: 3931

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.

Upvotes: 4

Related Questions