Group by Hour for Epoch time-CodePudding

I need to group by Hour for converted epoch time, here is what I have done but it is still grouping by the day. Can anyone support?

 select count (*) as "Total",to_char(trunc(to_date('19700101 00', 'YYYYMMDD HH24')   ( 1 / 24 / 60 / 60 ) * INSERTION_TIME),'DD/MM/YYYY HH24') as "Day"
from Records
where ID=35 and (INSERTION_TIME/86400)  to_date('01-01-1970 00:00:00','dd-mm-yyyy HH24:MI:SS') >=sysdate - 4
group by trunc(to_date('19700101 00', 'YYYYMMDD HH24')   ( 1 / 24 / 60 / 60 ) * INSERTION_TIME)
order by trunc(to_date('19700101 00', 'YYYYMMDD HH24')   ( 1 / 24 / 60 / 60 ) * INSERTION_TIME)

CodePudding user response：

You can get rid of most of the arithmetic and just use:

SELECT COUNT(*) as "Total",
       TO_CHAR(
         TRUNC(DATE '1970-01-01'   INTERVAL '1' SECOND * INSERTION_TIME, 'HH'),
         'DD/MM/YYYY HH24'
       ) as "Day"
FROM   Records
WHERE  ID=35
AND    DATE '1970-01-01'   INTERVAL '1' SECOND * INSERTION_TIME >=sysdate - 4
GROUP BY
       TRUNC(DATE '1970-01-01'   INTERVAL '1' SECOND * INSERTION_TIME, 'HH')
ORDER BY
       TRUNC(DATE '1970-01-01'   INTERVAL '1' SECOND * INSERTION_TIME, 'HH')

Note: Epoch time is usually 1970-01-01 00:00:00 UTC. If your session time zone is not UTC then you may be calculating the epoch time incorrectly and would want to use timestamps instead:

SELECT COUNT(*) as "Total",
       TO_CHAR(
         TRUNC(
           TIMESTAMP '1970-01-01 00:00:00 UTC'   INTERVAL '1' SECOND * INSERTION_TIME,
           'HH'
         ),
         'DD/MM/YYYY HH24'
       ) as "Day"
FROM   Records
WHERE  ID=35
AND    TIMESTAMP '1970-01-01 00:00:00 UTC'   INTERVAL '1' SECOND * INSERTION_TIME
         >=SYSTIMESTAMP - INTERVAL '4' DAY
GROUP BY
       TRUNC(
         TIMESTAMP '1970-01-01 00:00:00 UTC'   INTERVAL '1' SECOND * INSERTION_TIME,
         'HH'
       )
ORDER BY
       TRUNC(
         TIMESTAMP '1970-01-01 00:00:00 UTC'   INTERVAL '1' SECOND * INSERTION_TIME,
         'HH'
       )

CodePudding user response：

Notice the behavior of trunc on your expression. It removes the hour component.

Test case with dbfiddle.uk:

Now add the GROUP BY:

select COUNT(*) AS n
     , to_char(trunc(to_date('19700101 00', 'YYYYMMDD HH24')   ( 1 / 24 / 60 / 60 ) * INSERTION_TIME),'DD/MM/YYYY HH24') as "Day"
     , to_char(     (to_date('19700101 00', 'YYYYMMDD HH24')   ( 1 / 24 / 60 / 60 ) * INSERTION_TIME),'DD/MM/YYYY HH24') as "Day"
  from Records
 where ID = 35 and (INSERTION_TIME/86400)   to_date('01-01-1970 00:00:00','dd-mm-yyyy HH24:MI:SS') >= sysdate - 4
 group by      (to_date('19700101 00', 'YYYYMMDD HH24')   ( 1 / 24 / 60 / 60 ) * INSERTION_TIME)
 order by      (to_date('19700101 00', 'YYYYMMDD HH24')   ( 1 / 24 / 60 / 60 ) * INSERTION_TIME)
;

The grouped result:

To create a test case:

CREATE TABLE Records (
    id              int
  , insertion_time  int
);

INSERT INTO Records VALUES (35, 4011134567);
INSERT INTO Records VALUES (35, 4011134567);
INSERT INTO Records VALUES (35, 4011134567);
INSERT INTO Records VALUES (35, 4011134567);