Postgres and date subtracting

Imagine you have a table with two date(time) columns. Typicaly start and end. Let’s say it’s a table with opening hours of shops in your city. How do you select number of hours that a shop is open for?

Interval

Postgres has quite nifty data type called interval. However the output is relative to it’s length. It might be 1 dayor 2 weeks. For our purpose we do need the interval in fixed format  - in hours. This can be done with a bit of math. So the solution might be:

SELECT EXTRACT(epoch FROM (NOW() + interval '1 week' - NOW())) / 3600;

This snippet returns 168 because 168 hours is one week. For our case we can use sometghing like:

SELECT
    EXTRACT(epoch FROM ("end" - "start")) / 3600
FROM
    ...
;

Hope it will help you one day.