How can I aggregate the # of seconds in a query result?

Question

Hi everyone,

I have an Oracle 10g table that contains a # of log records. This table collects data from an environmental recording device that creates a log record every 15 seconds when it is running.

I want to add all of the seconds together from the reading dates of the log file entries to ensure that the logger ran > 24 hours (84,600 seconds). The data looks like this:

ID     READING_DATE 
286294 8/4/2010 1:03:30 PM 
286295 8/4/2010 1:03:45 PM 
286296 8/4/2010 1:04:00 PM 
286297 8/4/2010 1:04:15 PM 
286298 8/4/2010 1:04:30 PM 
286299 8/4/2010 1:04:45 PM 
286300 8/4/2010 1:05:00 PM 

I can't just query for the (end date - begin date), because the device may not run 24 hours consecutively. It may run for a total 24 hours over a period of 90 days.

What I want to do is write a query to be executed by the database that add each 15 second interval to the last to see if I have a result that is greater than > 84600.

Any/all replies are appreciated! Thanks in advance,

Answer 1

The number of seconds that the device has been running is approximately:

SELECT COUNT(*) * 15
FROM your_table

Answer 2

Assuming you have multiple devices logged to that table and you want to list all devices that have been running for > 1 day.

SELECT device_id
FROM log_table
GROUP BY device_id
HAVING MAX(reading_date) - MIN(reading_date) > 1.0