Postgres and date subtracting
Imagine you have a table with two date(time) columns. Typicaly
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?
Postgres has quite nifty data type called interval. However the output is relative to it’s length. It might be
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.