Sum of item count in an SQL query based on DATE_TRUNC

I’ve got a table which contains event status data, similar to this:

  ID             Time               Status
------  --------------------------  ------
357920  2019-12-25 09:31:38.854764    1
362247  2020-01-02 09:31:42.498483    1
362248  2020-01-02 09:31:46.166916    1
362249  2020-01-02 09:31:47.430933    1
362300  2020-01-03 09:31:46.932333    1
362301  2020-01-03 09:31:47.231288    1

I’d like to construct a query which returns the number of successful events each day, so:

         Time              Count
-------------------------- -----
2019-12-25 00:00:00.000000   1
2020-01-02 00:00:00.000000   3
2020-01-03 00:00:00.000000   2

I’ve stumbled across this SO answer to a similar question, but the answer there is for all the data returned by the query, whereas I need the sum grouped by date range.

Also, I cannot use BETWEEN to select a specific date range, since this query is for a Grafana dashboard, and the date range is determined by the dashboard’s UI. I’m using Postgres for the SQL dialect, in case that matters.

Answer

You need to remove the time from time component. In most databases, you can do this by converting to a date:

select cast(time as date) as dte, 
       sum(case when status = 1 then 1 else 0 end) as num_successful
from t
group by cast(time as date)
order by dte;

This assumes that 1 means “successful”.

The cast() does not work in all databases. Other alternatives are things like trunc(time), date_trunc('day', time), date_trunc(time, day) — and no doubt many others.

In Postgres, I would phrase this as:

select date_trunc('day', time) as dte, 
       count(*) filter (where status = 1) as num_successful
from t
group by dte
order by dte;