CODEWITHSUNDEEP
phpmysqlselect
Jim
Jim

Reputation: 2447

How to select top-of-the values from database with minute resolution

I have a mysql(i) databse that is written to every minute (usually 4 or 5 seconds after the minute).

I would like to select the values that are cleset to top-of-the-hour for the last 36 hours and I've no idea how to do it.

I've been playing with INTERVAL and DATE_ADD but have not found something that works yet. Any help would be appreciated.

Here's one of the rows that I'd like to select

Edit:

Extra info:
Table name:
temperature
Column names:
uid (AI)
time (timestamp)
probe0
probe1
probe2
probe3
probe4

Perhaps it would also be better to be
now
now -1hour
now -2hours
etc
now -36hours

FWIW, I'm currently using the following code so select ALL the data for the last 36 hours (2160 rows)

SELECT time, probe0, probe1, probe2, probe3, probe4 FROM temperature WHERE temperature.time >= DATE_ADD(NOW(), INTERVAL -2160 MINUTE) ORDER BY temperature.time DESC

Upvotes: 0

Views: 76

Answers (1)

codtex
codtex

Reputation: 6548

If you want to get the first record for each hour you can use MySQL's MIN() function and since it is an Aggregate Function you need to use GROUP BY to group your data by date and hour. So a sample query will look like:

SELECT 
    MIN(`time`) as first_for_hour, probe0, probe1, probe2, probe3, probe4
FROM `temperature`
WHERE `time` >= DATE_ADD(NOW(), INTERVAL -2160 MINUTE)
GROUP BY DATE(`time`), HOUR(`time`);

and for reading data 36 hours back I used your WHERE clause:

WHERE `time` >= DATE_ADD(NOW(), INTERVAL -2160 MINUTE)

I hope this will be helpful to you and here is the playground.

Upvotes: 1

Related Questions