1

I'm trying to generate a table that will show the number of tasks that were completed in each month.

The table has the following columns:

  • id
  • job_name
  • job_status
  • date_completed

My goal is to query the number of completed jobs per month between two values (e.g. 2021-02-01 [Feb 2021] to 2022-02-28 [Feb. 2021]), with the condition that it will only count those with status value '1' (1 = complete, 0 = ongoing) with the query producing this table:

Months   | Jobs Completed
---------+-----------------------
Feb 2020 | 0
Mar 2020 | 2
Apr 2020 | 0
...
Jan 2022 | 1
---------+------------------------

I managed to generate the series and have values to it, but the said table has the following problems:

  1. The order of the months are not arranged in ascending order (From Feb 2021 - Jan 2022, the generated series was random)
  2. The value of the months (the "jobs_completed") copies the highest value, rather than show their own. (e.g. if there are 3 jobs done in March 2021, and 0 on other months, all of the months will register their "jobs_completed" value as 3)

I have made a db-fiddle to show my problem: https://www.db-fiddle.com/f/gH7C7R6udMucjSATpw8Erh/0

SELECT to_char(generate_series('2021-02-01'::DATE, '2022-01-31'::DATE, '1 month'), 'Mon YYYY') AS month,
            COUNT(tbl.job_status) FILTER(WHERE job_status = 1) as jobs_completed
FROM monthly_tasks tbl
WHERE tbl.date_completed BETWEEN '2021-01-01' AND '2021-12-01'
GROUP BY month;

Thank you in advance!

2
  • Why are you using generate_series()?...is it possible not all months of the year exist in your table monthly_tasks?
    – J.D.
    Feb 1, 2021 at 13:50
  • I'll be using the query to feed a bar graph, which requires showing all the months in-between months, I was thinking the table will skip the month if it doesn't get any values returned by the query, which is why I used generate_series() Feb 1, 2021 at 14:50

3 Answers 3

0

The order of the months are not arranged in ascending order

To sort by date, the ORDER BY should be applied on the date or timestamp column. If the ORDER BY is applied on the result of to_char(), which is text, then the rows will be sorted alphabetically... and since August starts with an A, it will be first.

So, using the test tables from nbk's answer:

SELECT 
  to_char(year_month,'MON YYYY') as ym_text,
  coalesce(jobs_completed,0) AS jobs_completed
 FROM 
  (SELECT date_trunc('month',generate_series('2021-02-01'::DATE, '2022-01-31'::DATE, '1 month')) AS year_month) m
LEFT JOIN
  (SELECT
    date_trunc('month',date_completed) year_month,
    COUNT(*) as jobs_completed
  FROM monthly_tasks
  WHERE job_status = 1
  AND date_completed >='2021-02-01' AND date_completed < '2022-01-31'
  GROUP BY year_month) counts USING (year_month)
  ORDER BY year_month;

 ym_text  | jobs_completed
----------+----------------
 FEB 2021 |              0
 MAR 2021 |              2
 APR 2021 |              0
 MAY 2021 |              0
 JUN 2021 |              0
 JUL 2021 |              0
 AUG 2021 |              0
 SEP 2021 |              0
 OCT 2021 |              0
 NOV 2021 |              0
 DEC 2021 |              0
 JAN 2022 |              1

Another, muuuch simpler version using a subquery:

SELECT 
  to_char(year_month,'MON YYYY') as ym_text,
  (SELECT count(*) FROM monthly_tasks WHERE job_status=1
   AND date_completed >= year_month 
   AND date_completed < year_month + '1 month'::INTERVAL)
  FROM (SELECT date_trunc('month',generate_series('2021-02-01'::DATE, '2022-01-31'::DATE, '1 month')) AS year_month) m
  ORDER BY year_month;

You can fill the table with test data:

INSERT INTO monthly_tasks (date_completed, job_status) SELECT '2021-01-01'::DATE + '1 hour'::INTERVAL*generate_series(1,24*3650), (random()>0.5)::integer;
CREATE INDEX monthly_tasks_date_status ON monthly_tasks ( date_completed , job_status );
VACUUM ANALYZE monthly_tasks;

...and test query performance with EXPLAIN ANALYZE

First query: 5ms

First query with "date_completed >='2021-02-01' AND date_completed < '2022-01-31'" removed from the JOIN: 45ms

Second query: 1.8ms

Note the query planner isn't smart enough to figure that this returns 12 rows:

generate_series('2021-02-01'::DATE, '2022-01-31'::DATE, '1 month')

but it is smart enough to figure out that this one does:

'2021-02-01'::DATE + '1 MONTH'::INTERVAL*generate_series(0, 11))

...probably because the arguments are integers. So the latter may result in faster queries.

2
  • Thank you for your effort for this answer! While I've already decided to use a date dimensional table as suggested from an earlier response, I'll still mark this as the correct answer as this, well, answered my original question. Thank you again! :)) Feb 2, 2021 at 5:26
  • Thanks! You can use the dimension table in the queries above too.
    – bobflux
    Feb 2, 2021 at 8:31
1

I think I understand why you used generate_series() now. If you know your monthly_tasks table will always have all months in it, then you can write your query this way instead:

SELECT to_char(date_completed,'MON YYYY') AS month, SUM(CASE WHEN job_status = 1 THEN 1 ELSE 0) AS jobs_completed
FROM monthly_tasks tbl
WHERE tbl.date_completed BETWEEN '2021-01-01' AND '2021-12-01'
GROUP BY month;

If there's a chance your monthly_tasks table could be missing months, then you might want to create a dates table that has the all the months, and then left joining on (by month) to ensure you always have all the months. A dates table can be useful in a lot of future situations too.

Here's a pretty good boilerplate date's dimension table script you can use.

10
  • I see, won't it be more efficient to generate a series due to its dynamic nature (e.g. it has both months and years, and can be set between 2 ranges that can span, say, 2 years or more) rather than creating a dates table? Apologies for the question if the answer is obvious, I've just been starting to explore Postgresql and web dev. If I understand correctly, an additional table will take some space in the DB (tbf, the space is negligible), versus generating a series and then joining it the monthly_task as it's a one-and-go query. Thank you! Feb 1, 2021 at 15:44
  • @TechArtificer No problem, valid questions. Depends on what you mean by efficient. From a performance and runtime perspective, joining to a dates table will be as performant or possibly more performant than using the generate_series() function. It's actually a pretty common thing to create a dates table, as it has a lot of applicable places. In terms of space usage, yea it'll consume more space in the database, but for a table that spans everything since since the dawn of time to the year 9999 can take up as little as half a megabyte of space. That's not even worth a second thought.
    – J.D.
    Feb 1, 2021 at 16:13
  • 1
    I'm not sure if what I'm reading is right, but I've searched and read about Date Dimension Tables, is this what you're referring to? Feb 1, 2021 at 16:56
  • 1
    Reading then will implement it later after I understand and research more of it. Thank you for your help and new insight! I learned something new haha! Feb 1, 2021 at 16:59
  • 1
    I see, alright, will go with Dates Dimension. Thank you! Feb 2, 2021 at 1:28
0

You can Format the date wit TO_CHAR (see manual

CREATE TABLE monthly_tasks(
  id SERIAL PRIMARY KEY,
  job_name VARCHAR,
  date_completed DATE,
  job_status INT
  );
  
 INSERT INTO monthly_tasks (job_name, date_completed, job_status)
 VALUES
 ('Task 1', null, 0),
 ('Task 2', '2021-03-29', 1),
 ('Task 3', '2022-01-11', 1),
 ('Task 4', null, 0),
 ('Task 5', '2021-03-13', 1);
SELECT
to_char(date_completed,'MON YYYY') as year_month
, COUNT(*) as jobs_completed
FROM monthly_tasks
WHERE job_status = 1
GROUP BY year_month
ORDER BY MIN(date_completed)
year_month | jobs_completed
:--------- | -------------:
MAR 2021   |              2
JAN 2022   |              1

db<>fiddle here

1
  • Hi, this is my first query too, although I was wondering if there's a way to join it with a series as I am going to use it to feed a web app table (bar graph, range of 12 months). Thank you! Feb 1, 2021 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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