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.
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;