-1

in a Cx_Oracle 11g Database I have two columns: 'timestamp' and 'value'. I need a query to get the value average every one hour time. Which is the best way to do this? Timestamp is in seconds starting from epoch.

glc78
  • 439
  • 1
  • 8
  • 20

1 Answers1

1

If the TIMESTAMP column is of the TIMESTAMP data type then truncate the TIMESTAMP value to the hour and then group by that:

SELECT   TRUNC( timestamp_column, 'HH24' ) AS timestamp_hours,
         AVG( value ) AS average_value
FROM     your_table
GROUP BY TRUNC( timestamp_column, 'HH24' );

If:

Timestamp is in seconds starting from epoch.

SELECT   TRUNC( epoch_column / 3600 ) AS hours_from_epoch,
         AVG( value ) AS average_value
FROM     your_table
GROUP BY TRUNC( epoch_column / 3600 );

If you need to handle leap seconds then you will need a list of leap seconds (and to maintain it in the future) and then take this into account when working out the hours.

Community
  • 1
  • 1
MT0
  • 143,790
  • 11
  • 59
  • 117
  • I'm not the owner, how can I check the column data type? – glc78 Mar 21 '17 at 09:32
  • I've found it using `select data_type` and i see that `TIMESTAMP` is a NUMBER data_type. – glc78 Mar 21 '17 at 09:49
  • Thanks, it works perfectly! I suppose i just need to use different function when i need different aggregation(e.g. max, min) in the same slice of time. – glc78 Mar 21 '17 at 10:21