0

Question: I would like to create a query that can return a list of all days of the month with a sum of all duration for each day including zero for each day that has no data.

Database: PostgreSQL 9.x

Table structure (only relevant columns shown):

-------------------------------------------------------------
| id | start_at            | finish_at           | duration |
-------------------------------------------------------------
| 1  | 2015-08-14 14:01:00 | 2015-08-15 13:59:00 | 86280    |
-------------------------------------------------------------

I have the following query for grabbing the data I want but the part that is missing is the days between with zero and I'm not entirely sure on the best approach to achieve the desired outcome.

SELECT date_trunc('day', start_at) AS "day" , sum(duration) AS "duration"
FROM time_logs
JOIN users ON time_logs.user_id = users.id
JOIN account_users ON account_users.user_id = users.id
JOIN categories ON categories.id = time_logs.activity_id
WHERE account_users.account_id IN (1,2,3)
AND time_logs.start_at BETWEEN '2017-03-01 00:00:00 +1000' AND '2017-03-31 00:00:00 +1000'
AND categories.report_group = 'Segment1'
GROUP BY 1 
ORDER BY 1
  • 2
    take a look at this answer for generating a sequence of dates; you should be able to use said series to perform: date_series left join current_query, converting any sum(duration)=NULL to zero; if you still can't get it working then post back with an updated version of your working sql ... it shouldn't take much tweaking to get it to work – markp Sep 30 '17 at 1:13
  • BTW, "PostgreSQL 9.x" does not say much. See: postgresql.org/support/versioning – Erwin Brandstetter Sep 30 '17 at 6:04
  • @ErwinBrandstetter I think the comment meant the OP's own SQL and not the server version! – Vérace Sep 30 '17 at 6:28
2

Add an outer join to a complete series of days using generate_series():

SELECT day, COALESCE(duration, 0) AS duration
FROM  (  -- your original query, just added table aliases to reduce noise
         -- and fixed the upper bound of your time range
   SELECT date_trunc('day', t.start_at) AS day
        , sum(duration) AS duration  -- add table qualification to duration
   FROM   time_logs     t
   JOIN   users         u  ON t.user_id = u.id
   JOIN   account_users a  ON a.user_id = u.id
   JOIN   categories    c  ON c.id = t.activity_id
   WHERE  a.account_id IN (1,2,3)
   AND    t.start_at >='2017-03-01 00:00 +1'
   AND    t.start_at < '2017-04-01 00:00 +1' -- to get the whole month
   AND    c.report_group = 'Segment1'
   GROUP  BY 1 
   ) sub
RIGHT  JOIN generate_series(timestamptz '2017-03-01 00:00 +1'
                                      , '2017-03-31 00:00 +1'  -- !
                                      , '1 day') day USING (day)
ORDER  BY 1;

And COALESCE to get 0 ("zero") instead of NULL, if you need that.

BETWEEN '2017-03-01 00:00 +1' AND '2017-03-31 00:00 +1' would exclude the last day of march (except for its very first µs). I changed it to include the whole month. AND t.start_at < '2017-04-01 00:00 +1' has the additional advantage that you don't need to adapt the day of the upper bound, just the month.

Since start_at is obviously timestamptz, that's the most efficient call with matching data type. Detailed explanation here:

The actual data type of start_at and your actual time zone setting might lead to disagreement when days start. Consider:

  • +1 - exactly what I was grasping for. I did have it done with CTEs, but I just couldn't get it over the finishing line! – Vérace Sep 30 '17 at 8:27
  • @Vérace: Yes, a CTE would be another option. Typically a bit more expensive than a subquery in Postgres since the derived table is materialized. That's why I favor subqueries where possible. – Erwin Brandstetter Sep 30 '17 at 14:56
  • @ErwinBrandstetter thanks this has helped a lot, still learning a lot as Ruby on Rails has abstracted this away for many years since doing MSSQL... Just stuck on something your RIGHT join returns all zeros and changing to a LEFT join only returns days with data. What am I missing? – Daniel Draper Oct 1 '17 at 13:14
  • @DanielDraper: generate_series() as first FROM item, followed by a LEFT [OUTER] JOIN to your subquery would be equivalent. All zeroes? date_trunc() necessarily understands "days" according to the current time zone setting. Your question shows timestamp and timestamptz literals. Every question like this should start with an exact table definition showing data types and constraints: CREATE TABLE ..., and your exact version of Postgres (like I hinted already). I added some more info to the answer. – Erwin Brandstetter Oct 1 '17 at 22:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.