Counts for time range per day-CodePudding

I have a table something like this

create table widgets
(
   id primary key,
   created_at timestamp,
   -- other fields
)

Now I want a query that shows the count of widgets with created_at between multiple time ranges for each day. For example, the count of widgets with created_at between 00:00:00 and 11:59:59 and the count between 12:00:00 and 23:59:59. The output would look something like this:

date           | morning widgets (before noon) | evening widgets (after noon) |
---------------|-------------------------------|------------------------------|
2022-05-01     |            ##                 |           ##                 |
2022-05-02     |            ##                 |           ##                 |
2022-05-03     |            ##                 |           ##                 |
2022-05-04     |            ##                 |           ##                 |
... etc.

So far, I figured out I can get counts per day:

select created_at::date as created_at_date, count(*) as total
from widgets
where created_at::date >= '2022-05-01' -- where clause for illustration purposes only and not critical to the central question here
group by created_at::date

I'm learning about windowing functions, specifically partition by. I think this will help me get what I want, but not sure. How do I do this?

I'd prefer a "standard SQL" solution. If necessary, I'm on postgres and can use anything specific to its flavor of SQL.

CodePudding user response：

If I understand correctly, we can try to use the condition window function to make it.

morning widgets (before noon) : between 00:00:00 and 11:59:59
evening widgets (after noon) : between 12:00:00 and 23:59:59

put the condition in aggregate function by CASE WHEN expression.

SELECT created_at::date,
       COUNT(CASE WHEN created_at >= created_at::date AND created_at <=  created_at::date   INTERVAL '12 HOUR' THEN 1 END) ,
       COUNT(CASE WHEN created_at >= created_at::date   INTERVAL '12 HOUR' AND  created_at <= created_at::date  INTERVAL '1 DAY' THEN 1 END)
FROM  widgets w
GROUP BY created_at::date
ORDER BY created_at::date

sqliddle