Home » SQL & PL/SQL » SQL & PL/SQL » group timestamp column hourly
group timestamp column hourly [message #669275] |
Wed, 11 April 2018 10:24 |
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 |
cookiemonster
Messages: 13925 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 #669278 is a reply to message #669277] |
Wed, 11 April 2018 13:00 |
|
Michel Cadot
Messages: 68658 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
|
|
|
Goto Forum:
Current Time: Tue Jun 04 22:15:08 CDT 2024
|