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.
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.
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 ( values (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 🙂