How to group by hour in PostgreSQL using "time" field?

Grouping on time: leaving minutes and seconds behind

The date_trunc('hour', your_time_column) subsets your "time" field, purposefully disregarding minutes and seconds for a cleaner, hour-focused analysis.

Harnessing dates and times together

In cases where your dataset carries both date and time components, you can easily group by both:

-- Ta-da! Magic trick: handling dates and time together
SELECT 
  cdate, 
  date_trunc('hour', ctime) AS hour, 
  COUNT(*)
FROM your_table
GROUP BY cdate, hour;


Such a query empowers you to examine trends across various days, giving each hour its own deserving spotlight.

An alternative: the date_part method

In situations where date_trunc feels less intuitive, venturing into the realm of date_part could be of help:

-- Because one magic trick is never enough, right?
SELECT 
  date_part('hour', ctime) AS hour,
  COUNT(*)
FROM your_table
GROUP BY hour;


Beware, date_part returns a floating-point number. So, don't be startled to see numbers requiring rounding!

Mastering time formatting and aggregation

As you begin to wield these methodologies, consider the end appearance of your data. Do you need a more readable output? Perhaps rounded-off minutes? Such customization is easily achievable:

SELECT 
  to_char(date_trunc('hour', ctime), 'HH24:MI') AS hour,
  COUNT(*) AS event_count
FROM your_table
GROUP BY hour;


The to_char function puts the reins of formatting in your hands. Here, we've easily transformed raw times into a more consumer-friendly HH24:MI format.

Unravel time without a time zone

SQL's ctime column often displays as "time without time zone" type. This makes it a great candidate for simpler time-based grouping without the clutter of varying time zones.

Troubleshooting time zone troubles

Dealing with multiple time zones or daylight saving shifts can complicate grouping, but fear not, PostgreSQL's AT TIME ZONE clause is there for the rescue:

-- Because goblins need to group by time even in Goblin Standard Time
SELECT date_trunc('hour', ctime AT TIME ZONE 'EST') AS hour, COUNT(*)
FROM your_table
GROUP BY hour;


Ghosts of common pitfalls

When using date_trunc and date_part, stay on guard for unexpected, sneaky errors. Time boundaries can play tricks on the best of us, that's why testing your queries thoroughly is good practice.