How do I pull values between two datetimes at specific interval in MySQL?

Question

I have an application that writes temperature values to a MySQL table every second, It consists of the temperature and a datetime field.

I need to pull these values out of the table at specific intervals, every second, minute, hour etc.

So for example I will need to pull out values between 2 datetime fields and show the temperature at the hour for each of them.

One option I've considered is to create a temporary table that holds a list of the time intervals generated using MySQL INTERVAL and then joining that against the main table.

I'm just wondering if there's some time and date functions in MySQL that I'm overlooking that would make this easier?

Thanks.

Answer 1

You could use between for your date, and then a conditional WHERE clause using time() that looks at the structure of the timestamp. If it has 00:00 (for instance, 16:00:00) within it, take it, if not, leave it.

Example (untested):

SELECT temp, date
FROM temperatures
WHERE (date BETWEEN '2009/01/03 12:00:00' AND '2009/01/04 12:00:00') 
 AND  (time(date) LIKE '%:00:00')
ORDER BY date ASC
LIMIT 10