Hourly total of table entries over one day

Question

I have a table mytable with an event time field called evtime. To summarize the number of entries per day, I use:

select date_trunc('day',evtime),
       count(1) as entries 
from mytable 
group by date_tunc('day',evtime);

That works fine. But now, for one specific date, I'd like to see the totals per hour.

Answer 1

Is this what you mean?

select date_trunc('hour', evtime),
       count(*) as entries 
from mytable 
where date_trunc('day', evtime) = $yourdate
group by date_tunc('hour', evtime);

Or, it occurs to me that you want the summary by day on most days and by hour on one. If so:

select (case when date_trunc('day', evtime) = $yourdate
             then date_trunc('hour', evtime)
             else date_trunc('day', evtime)
        end) as day_hour,
       count(*) as entries 
from mytable 
group by day_hour;