I have simple table
discount_code | updated_date
----------------------------
L1 | 2017-02-01 06:49:27
L1 | 2017-02-01 09:35:39
L1 | 2017-02-01 09:51:41
//etc
I want result in PostgreSQL like below,
time_range | count
----------------------------
00:00-01:00 | 0
01:00-02:00 | 0
//etc
06:00-07:00 | 1
09:00-10:00 | 2
I want count hours wise record.My exact concept is to plot a graph hours wise with count.I am trying below query but not working,
select count(range) as ranges,
case
when to_char(updated_date,'HH:MI') >=00:00 and to_char(updated_date,'HH:MI')<=01:00 then '00:00-01:00'
when to_char(updated_date,'HH:MI') >=01:00 and to_char(updated_date,'HH:MI')<=02:00 then '01:00-02:00'
when to_char(updated_date,'HH:MI') >=02:00 and to_char(updated_date,'HH:MI')<=03:00 then '02:00-03:00'
when to_char(updated_date,'HH:MI') >=03:00 and to_char(updated_date,'HH:MI')<=04:00 then '03:00-04:00'
when to_char(updated_date,'HH:MI') >=04:00 and to_char(updated_date,'HH:MI')<=05:00 then '04:00-05:00'
when to_char(updated_date,'HH:MI') >=05:00 and to_char(updated_date,'HH:MI')<=06:00 then '05:00-06:00'
when to_char(updated_date,'HH:MI') >=06:00 and to_char(updated_date,'HH:MI')<=07:00 then '06:00-07:00'
when to_char(updated_date,'HH:MI') >=07:00 and to_char(updated_date,'HH:MI')<=08:00 then '07:00-08:00'
when to_char(updated_date,'HH:MI') >=08:00 and to_char(updated_date,'HH:MI')<=09:00 then '08:00-09:00'
when to_char(updated_date,'HH:MI') >=09:00 and to_char(updated_date,'HH:MI')<=10:00 then '09:00-10:00'
when to_char(updated_date,'HH:MI') >=10:00 and to_char(updated_date,'HH:MI')<=11:00 then '10:00-11:00'
when to_char(updated_date,'HH:MI') >=11:00 and to_char(updated_date,'HH:MI')<=12:00 then '11:00-12:00'
when to_char(updated_date,'HH:MI') >=12:00 and to_char(updated_date,'HH:MI')<=13:00 then '12:00-13:00'
//etc
else '' end AS range from
from my_table
where date(updated_date)=='2017-02-01'