CODEWITHSUNDEEP
phppostgresql
bdevloper
bdevloper

Reputation: 43

PostgreSQL hours range wise count data

    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'

Upvotes: 0

Views: 418

Answers (3)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

What about something like this, You can format the hour range with another date function.

DEMO

SELECT date_trunc('hour', "updated_date") as hour, count(*)
FROM Table1
GROUP BY date_trunc('hour', "updated_date")
ORDER BY 1

OUTPUT

enter image description here

EDIT: a more complete version need you calculate the ranges.

DEMO 2:

WITH parameter as (
    SELECT '2017-02-01'::date as d
), hours (id, t) as (
    SELECT 1, '00:00'::time t UNION ALL
    SELECT 2, '01:00'::time UNION ALL
    SELECT 3, '02:00'::time UNION ALL
    SELECT 4, '03:00'::time UNION ALL
    SELECT 5, '04:00'::time UNION ALL
    SELECT 6, '05:00'::time UNION ALL
    SELECT 7, '06:00'::time UNION ALL
    SELECT 8, '07:00'::time UNION ALL
    SELECT 9, '08:00'::time UNION ALL
    SELECT 10, '09:00'::time UNION ALL
    SELECT 11, '10:00'::time UNION ALL
    SELECT 12, '11:00'::time UNION ALL
    SELECT 13, '12:00'::time UNION ALL
    SELECT 14, '13:00'::time UNION ALL
    SELECT 15, '14:00'::time UNION ALL
    SELECT 16, '15:00'::time UNION ALL
    SELECT 17, '16:00'::time UNION ALL
    SELECT 18, '17:00'::time UNION ALL
    SELECT 19, '18:00'::time UNION ALL
    SELECT 20, '19:00'::time UNION ALL
    SELECT 21, '20:00'::time UNION ALL
    SELECT 22, '21:00'::time UNION ALL
    SELECT 23, '22:00'::time UNION ALL
    SELECT 24, '23:00'::time UNION ALL
    SELECT 25, '24:00'::time
), ranges as (
SELECT d + h1.t from_time,
       d + h2.t to_time
FROM parameter
CROSS JOIN hours h1
INNER JOIN hours h2
        ON h1.id = h2.id - 1
)    
SELECT from_time, to_time, count(t.updated_date)
FROM ranges r
LEFT JOIN Table1 t
  ON t.updated_date >= r.from_time
 AND t.updated_date < r.to_time
GROUP BY from_time, to_time
ORDER BY from_time

OUTPUT

enter image description here

Upvotes: 1

pozs
pozs

Reputation: 36244

Use the generate_series() function to get results where you have no data:

select    to_char(h, 'HH24:MI-') || to_char(h + interval '1 hour', 'HH24:MI') time_range,
          count(discount_code)
from      generate_series(timestamp '2017-02-01',
                          timestamp '2017-02-02' - interval '1 hour',
                          interval '1 hour') h
left join simple_table on updated_date >= h and updated_date < (h + interval '1 hour')
group by  h
order by  h;

http://rextester.com/EBBCL23865

Upvotes: 0

krokodilko
krokodilko

Reputation: 36117

Try:

select lpad( hr::text, 2, '0' )||':00-'||lpad( (hr+1)::text, 2, '0' )||':00' as time_range,
       count(discount_code)
from (
        select *
        from generate_series(0,23) hr
) x
left join my_table t on x.hr = extract( hour from t.UPDATED_DATE)
group by lpad( hr::text, 2, '0' )||':00-'||lpad( (hr+1)::text, 2, '0' )||':00'

Upvotes: 0

Related Questions