CODEWITHSUNDEEP
sqlpostgresql
JonRed
JonRed

Reputation: 2971

Aggregate results split by day

I'm trying to write a query that returns summarised data, per day, over many day's of data. For example

| id | user_id | start 
|----|---------|------------------------------
| 1  | 1       | 2020-02-01T17:35:37.242+00:00
| 2  | 1       | 2020-02-01T13:25:21.344+00:00
| 3  | 1       | 2020-01-31T16:42:51.344+00:00
| 4  | 1       | 2020-01-30T06:44:55.344+00:00

The outcome I'm hoping for is a function that I can pass in a the userid and timezone, or UTC offset, and get out:

 | day     | count |
 |---------|-------|
 | 1/2/20  | 2     |
 | 31/1/20 | 1     |
 | 30/1/20 | 7     |

Where the count is all the rows that have a start time falling between 00:00:00.0000 and 23:59:59.9999 on each day - taking into consideration the supplied UTC offset.

I don't really know where to start writing a query like this, and I the fact I can't even picture where to start feels like a big gap in my SQL thinking. How should I approach something like this?

Upvotes: 0

Views: 69

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269843

You can use:

select date_trunc('day', start) as dte, count(*)
from t
where userid = ?
group by date_trunc('day', start)
order by dte;

If you want to handle an additional offset, build that into the query:

select dte, count(*)
from t cross join lateral
     (values (date_trunc('day', start + ? * interval '1 hour'))) v(dte)
where userid = ?
group by v.dte
order by v.dte;

Upvotes: 1

Related Questions