CODEWITHSUNDEEP
postgresqltime-seriesgrafana
Karl Adler
Karl Adler

Reputation: 16836

Postgres, Count entries per day including days with none

I have a table with a few fields, but I just want to count the entries per day where the state field is e.g. 'rejected'. The field createdAt is a timestamp with timezone.

id | createdAt                  | state
------------------------------------------
1  | 2018-01-17 13:53:17.303+00 | rejected
2  | 2017-10-19 20:39:18.232+00 | approved

So I want to count the rows for each day between two timestamps. Days without any entry should return 0 or NULL. The query I created returns the correct time series, but somehow my join or count seems to be odd. It returns me thousands of entries, but there are usually just between 0 and 20. (Whole table is less than 3800 entries)

SELECT serie.day AS time, count(t."createdAt") AS created
FROM (
   SELECT generate_series(
       to_timestamp(1518248353)::date,
       to_timestamp(1521469246)::date, 
       interval '1 day'
   ) AS day
   FROM users t
) serie
LEFT JOIN users t ON t."createdAt"::date = serie.day::date 
     WHERE state ILIKE 'rejected'
GROUP  BY serie.day
ORDER  BY time;

Wrong result:

2018-02-10 00:00:00+00  11619
2018-02-11 00:00:00+00  23238
2018-02-12 00:00:00+00  27111
2018-02-13 00:00:00+00  19365
2018-02-14 00:00:00+00  15492
2018-02-15 00:00:00+00  34857
2018-02-16 00:00:00+00  34857
2018-02-17 00:00:00+00  15492
2018-02-18 00:00:00+00  46476
2018-02-19 00:00:00+00  15492
2018-02-21 00:00:00+00  27111
2018-02-22 00:00:00+00  19365
2018-02-23 00:00:00+00  11619
2018-02-24 00:00:00+00  3873
2018-02-25 00:00:00+00  3873
...

Expected result something like this:

2018-02-10 00:00:00+00  3
2018-02-11 00:00:00+00  9
2018-02-12 00:00:00+00  0
2018-02-13 00:00:00+00  19
2018-02-14 00:00:00+00  15
2018-02-15 00:00:00+00  3
2018-02-16 00:00:00+00  7
...

Upvotes: 2

Views: 3022

Answers (1)

e_i_pi
e_i_pi

Reputation: 4820

Why are you joining your generated series to the user table in the subquery? I think that's your problem. Normally, when you use generate_series() you just provide start datetime, end datetime, and interval, and it generates that series as a table.

Try this instead:

SELECT
    serie.day AS time,
    COUNT(t."createdAt") AS created
FROM (
    SELECT date_series::date AS day
    FROM generate_series(
        to_timestamp(1518248353)::date,
        to_timestamp(1521469246)::date,
        '1 day'
    ) AS date_series
) AS serie
LEFT JOIN users t ON t."createdAt"::date = serie.day::date 
    AND state ILIKE 'rejected'
GROUP  BY serie.day
ORDER  BY time;

Upvotes: 5

Related Questions