servabat servabat - 2 years ago 102
SQL Question

Extract time from interval

How to extract time from an interval row ?

e.g. '1 days 2 hours 30 minutes' and I want to get only '2 hours 30 minutes'

I need this to cast interval to a time row, maybe is there better ways ?

Answer Source

You can use extract:

select extract(hour from interval_column), 
       extract(minute from interval_column)
from the_table

If you want convert that to a real time value you can use:

select make_time(extract(hour from interval_column)::int, 
                 extract(minute from interval_column)::int, 0)
from the_table

The above can fail, because an interval can contain more then 24 hours. In that case you would need to use justify_interval - but it does sound very strange:

select make_time(extract(hour from justify_interval(interval_column))::int, 
                 extract(minute from justify_interval(interval_column))::int, 0)
from the_table

This would convert interval '100' hour into the time 04:00 - whatever you are going to do with that

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download