CODEWITHSUNDEEP
sqloracle-databasetimestamp
redsoxlost
redsoxlost

Reputation: 1235

How to add minutes to a timestamp?

I have a table which has data in 5 minute intervals i.e. 5 10 15 20 ...

The table has 3 columns (id number, log_ts date , count number) and has millions of rows. So far I have been using the below query to sum up the "count" column for a particular date .

I need to aggregate on an hourly level 

select id, SUM(count)
     log_ts + DECODE(TO_CHAR(log_ts , 'MI'), '05', 55/1440, '10', 50/1440, '15', 45/1440, '20',40/1440,'25',35/1440,'30',30/1440,'35',25/1440,'40',20/1440,'45',15/1440,'50',10/1440,'55',5/1440,0) TS
from example_table 
Group by id,
log_ts + DECODE(TO_CHAR(log_ts , 'MI'), '05', 55/1440, '10', 50/1440, '15', 45/1440, '20',40/1440,'25',35/1440,'30',30/1440,'35',25/1440,'40',20/1440,'45',15/1440,'50',10/1440,'55',5/1440,0)

Is there any other way to aggregate a time column at an hourly level which has data in 5 minutes level.

Upvotes: 0

Views: 867

Answers (2)

Dba
Dba

Reputation: 6639

Try like this,

SELECT id, trunc(log_ts, 'HH'), sum(count)
FROM   example_table
GROUP BY id, trunc(log_ts, 'HH');

Upvotes: 0

Ben
Ben

Reputation: 52853

You need to use TRUNC(), which can truncate a date/number etc to the unit of time specified.

For hourly, the format model is HH. This makes your query:

select trunc(log_ts, 'HH'), sum(count)
  from example_table
 group by trunc(log_ts, 'HH')

I assume the name of your column isn't actually COUNT. If it is I would recommend changing this to something that isn't a keyword.

Upvotes: 2

Related Questions