Skip to main content
Database Administrators

You are not logged in. Your edit will be placed in a queue until it is peer reviewed.

We welcome edits that make the post easier to understand and more valuable for readers. Because community members review edits, please try to make the post substantially better than how you found it, for example, by fixing grammar or adding additional resources and hyperlinks.

Required fields*

6
  • First of all thank you for your answer. For ID 6 in fact it's a mistake on my part on the dow. Every day in my initial request must be the same. The days of the week in the entry are in the clause : AND (EXTRACT(dow FROM "billing_transaction". "begin_date" AT TIME ZONE 'Europe/Paris') + 1 in (1) OR EXTRACT(dow FROM "billing_transaction". "end_date" AT TIME ZONE 'Europe/Paris') + 1 in (1) ) I've edited my question and correct data. Your awser is good Commented Apr 16, 2020 at 9:43
  • The results of total_included look good, however, I'd like to add up all the events to get a total duration. I was thinking sum(sum..) but that's not possible Commented Apr 16, 2020 at 9:44
  • @Hadrien: you can remove the group by id order by id to have a grand total. Or you can move the whole query into a subquery and sum total_included at the top level. Commented Apr 16, 2020 at 11:36
  • @Hadrien: when substracting a range from a bigger range that entirely contains it, the result is two ranges, so that can't fit into a single tstzrange value. But if excluded = (end_date-begin_date) - included, why bother with a more complicated way to compute it? Commented Apr 17, 2020 at 10:39
  • Yes I understand, but for total_excluded for a event with begin_date 2019-12-01 18:00:00 and end_date 2019-12-02 08:00:00 with filter start_time 18:00and end_time 23:00and dow Sunday the result is 1 hour because all the time of the monday not matter because of dow filter Commented Apr 17, 2020 at 12:57