# Tutorial :How do I convert an interval into a number of hours with postgres? ### Question:

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)  ``

### Solution:1

Probably the easiest way is:

``SELECT EXTRACT(epoch FROM my_interval)/3600  ``

### Solution:2

If you want integer i.e. number of days:

``SELECT (EXTRACT(epoch FROM (SELECT (NOW() - '2014-08-02 08:10:56')))/86400)::int  ``

### Solution:3

To get the number of days the easiest way would be:

``SELECT EXTRACT(DAY FROM NOW() - '2014-08-02 08:10:56');  ``

As far as I know it would return the same as:

``SELECT (EXTRACT(epoch FROM (SELECT (NOW() - '2014-08-02 08:10:56')))/86400)::int;  ``

### Solution:4

`select floor((date_part('epoch', order_time - '2016-09-05 00:00:00') / 3600)), count(*) from od_a_week group by floor((date_part('epoch', order_time - '2016-09-05 00:00:00') / 3600));`

The `::int` conversion follows the principle of rounding. If you want a different result such as rounding down, you can use the corresponding math function such as `floor`.

### Solution:5

If you convert table field:

1. Define the field so it contains seconds: ` CREATE TABLE IF NOT EXISTS test ( ... field INTERVAL SECOND(0) ); `
2. Extract the value. Remember to cast to int other wise you can get an unpleasant surprise once the intervals are big: ` EXTRACT(EPOCH FROM field)::int `

### Solution:6

``         select date 'now()' - date '1955-12-15';  ``

Here is the simple query which calculates total no of days.

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »