According to your question working hours are: Mo–Fr, 08:00–15:00. Rounded results For just two given timestamps Operating on units of 1 hour. Fractions are ignored, therefore not precise but simple: SELECT count(*) AS work_hours FROM generate_series (timestamp ‘2013-06-24 13:30’ , timestamp ‘2013-06-24 15:29’ – interval ‘1h’ , interval ‘1h’) h WHERE EXTRACT(ISODOW FROM h) < … Read more
Table layout Re-design the table to store opening hours (hours of operation) as a set of tsrange (range of timestamp without time zone) values. Requires Postgres 9.2 or later. Pick a random week to stage your opening hours. I like the week: 1996-01-01 (Monday) to 1996-01-07 (Sunday) That’s the most recent leap year where Jan … Read more
Range types consist of lower and upper bound, and each can be included or excluded. The canonical form (and default for range types) is to include the lower and exclude the upper bound. Inclusive bounds ‘[]’ You could include lower and upper bound ([]), and enforce it with a CHECK constraint using range functions. Then … Read more