How do I convert an interval into a number of hours with postgres?

datetime postgresql intervals
agnul picture agnul · Jun 4, 2009 · Viewed 159.1k times · Source

Say I have an interval like

4 days 10:00:00

in postgres. How do I convert that to a number of hours (106 in this case?) Is there a function or should I bite the bullet and do something like

extract(days, my_interval) * 24 + extract(hours, my_interval)

Answer

Magnus Hagander picture Magnus Hagander · Jun 4, 2009

Probably the easiest way is:

SELECT EXTRACT(epoch FROM my_interval)/3600

Related questions

PostgreSQL how to concat interval value '2 days'

In PostgreSQL I want to concat the current_timestamp with an interval as follows: select current_timestamp + interval 2||' days' But when I do, I get an error: [Err] ERROR: syntax error at or near "2" LINE 1: select current_timestamp + interval 2||…

Read More
Can't subtract offset-naive and offset-aware datetimes

I have a timezone aware timestamptz field in PostgreSQL. When I pull data from the table, I then want to subtract the time right now so I can get it's age. The problem I'm having is that both datetime.datetime.…

Read More
CURRENT_TIMESTAMP in milliseconds

Is there any way to get milliseconds out of a timestamp in MySql or PostgreSql (or others just out of curiosity)? SELECT CURRENT_TIMESTAMP --> 2012-03-08 20:12:06.032572 Is there anything like this: SELECT CURRENT_MILLISEC --> 1331255526000 or the …

Read More