How to catagorize timestamp based on hours in postgreSQL

All we need is an easy explanation of the problem, so here it is.

I am trying to categorize the timestamp by the HOUR using the arrival column categories like [‘00.00 – 06.00’, ‘06.00 – 10.00’, ‘10.00 – 14.00’, ‘14.00 – 18.00’, ‘18.00 – 00.00’]). I am very new to this kindly help.

How to catagorize timestamp based on hours in postgreSQL

the table has all the time durations from 00.00 to 23.00 just the sample is shown here.

Kindly help me to do the categories and add them.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You can build a list of start/end times and do a left join against that together with a group by:

Something along the lines:

with times (begin_time, end_time) as (
     (time '00:00', time '06:00'),
     (time '06:00', time '10:00'),
     (time '10:00', time '14:00'),
     (time '14:00', time '18:00'),
     (time '18:00', time '24:00')
select tm.begin_time, tm.end_time, count(*)
from times tm
  left join the_table t 
         on t.arrival::time >= tm.begin_time
        and t.arrival::time < tm.end_time
group by tm.begin_time, tm.end_time
order by 1,2;        

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from or, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply