sqlpostgresqlpostgresql-9.0

Hourly total of table entries over one day


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.


Solution

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