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

Question

Probably the easiest way is:

SELECT EXTRACT(epoch FROM my_interval)/3600

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

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

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;

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.

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