Home » SQL & PL/SQL » SQL & PL/SQL » group timestamp column hourly
group timestamp column hourly [message #669275] Wed, 11 April 2018 10:24 Go to next message
dinavahi.saradhi@gmail.co
Messages: 8
Registered: December 2008
Junior Member
I have the below table data with timestamp column


  with

  inputs ( flight, gate, ts ) as (

    select 1,   1, TO_TIMESTAMP ('10-Sep-02 01:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') from dual union all

    select 2, 101, TO_TIMESTAMP ('10-Sep-02 01:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') from dual union all

    select 3,   2, TO_TIMESTAMP ('10-Sep-02 02:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') from dual union all

    select 4, 202, TO_TIMESTAMP ('10-Sep-02 03:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') from dual union all

    select 5,   3, TO_TIMESTAMP ('10-Sep-02 03:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') from dual union all

    select 6, 303, TO_TIMESTAMP ('10-Sep-02 04:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') from dual

  )

  select trunc(ts,'HH'), count(*) from inputs

  group by trunc(ts,'HH');




Output is below


TRUNC(TS,'HH')   COUNT(*)

-------------- ----------

10-SEP-02               2

10-SEP-02               2

10-SEP-02               1

10-SEP-02               1




But I want something like this:




TRUNC(TS,'HH')   COUNT(*)

-------------- ----------

10-SEP-02  01 Hr            2

10-SEP-02  02 Hr            1

10-SEP-02  03 Hr            2

10-SEP-02  04 Hr            1

Re: group timestamp column hourly [message #669276 is a reply to message #669275] Wed, 11 April 2018 10:29 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
SQL> with
  2    inputs ( flight, gate, ts ) as (
  3      select 1,   1, TO_TIMESTAMP ('10-Sep-02 01:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') from dual union all
  4      select 2, 101, TO_TIMESTAMP ('10-Sep-02 01:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') from dual union all
  5      select 3,   2, TO_TIMESTAMP ('10-Sep-02 02:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') from dual union all
  6      select 4, 202, TO_TIMESTAMP ('10-Sep-02 03:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') from dual union all
  7      select 5,   3, TO_TIMESTAMP ('10-Sep-02 03:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') from dual union all
  8      select 6, 303, TO_TIMESTAMP ('10-Sep-02 04:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') from dual
  9    )
 10    select to_char(trunc(ts,'HH'), 'DD-MON-YY HH24')|| 'Hr', count(*) from inputs
 11    group by trunc(ts,'HH')
 12    order by trunc(ts,'HH');
 
TO_CHAR(TRUNC(TS,'HH'),'DD-MON   COUNT(*)
------------------------------ ----------
10-SEP-02 01Hr                          2
10-SEP-02 02Hr                          1
10-SEP-02 03Hr                          2
10-SEP-02 04Hr                          1
 
SQL> 
Re: group timestamp column hourly [message #669277 is a reply to message #669276] Wed, 11 April 2018 12:44 Go to previous messageGo to next message
dinavahi.saradhi@gmail.co
Messages: 8
Registered: December 2008
Junior Member
thanks cookiemonster .. this is correct
Re: group timestamp column hourly [message #669278 is a reply to message #669277] Wed, 11 April 2018 13:00 Go to previous message
Michel Cadot
Messages: 67303
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Just a small remark you can put the "Hr" part directly inside the datetime format (and avoid the concatenation):
SQL> with
  2    inputs ( flight, gate, ts ) as (
  3      select 1,   1, TO_TIMESTAMP ('10-Sep-02 01:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') from dual union all
  4      select 2, 101, TO_TIMESTAMP ('10-Sep-02 01:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') from dual union all
  5      select 3,   2, TO_TIMESTAMP ('10-Sep-02 02:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') from dual union all
  6      select 4, 202, TO_TIMESTAMP ('10-Sep-02 03:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') from dual union all
  7      select 5,   3, TO_TIMESTAMP ('10-Sep-02 03:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') from dual union all
  8      select 6, 303, TO_TIMESTAMP ('10-Sep-02 04:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF') from dual
  9    )
 10  select to_char(trunc(ts,'HH'), 'DD-MON-YY HH24"Hr"'), count(*) from inputs
 11  group by trunc(ts,'HH')
 12  order by trunc(ts,'HH');
TO_CHAR(TRUNC(   COUNT(*)
-------------- ----------
10-SEP-02 01Hr          2
10-SEP-02 02Hr          1
10-SEP-02 03Hr          2
10-SEP-02 04Hr          1
Previous Topic: 365 days always
Next Topic: multiple selects after with clauses
Goto Forum:
  


Current Time: Fri Aug 14 05:23:59 CDT 2020