Sampling SQL timeseries

I have a timeseries of datetime, double columns stored in mySQL and would like to sample the timeseries every minute (开发者_如何学JAVAi.e. pull out the last value at one minute intervals). Is there an efficient way of doing this in one select statement?

The brute force way would involve either selecting the whole series and doing the sampling on the client side or sending one select for each point (e.g. select * from data where timestamp<xxxxxxxxx order by timestamp desc limit 1).

You could convert your timestamps to UNIX timestamps, group by unix_timestamp DIV 60 and pull the maximum timestamps from each group. Afterwards join the obtained list back to the original table to pull the data for the obtained timestamps.

Basically it might look something like this:

SELECT
  t.*  /* you might want to be more specific here */
FROM atable t
  INNER JOIN (
    SELECT
      MAX(timestamp) AS timestamp
    FROM atable
    GROUP BY UNIX_TIMESTAMP(timestamp) DIV 60
  ) m ON t.timestamp = m.timestamp

You can use DATE_FORMAT to get just the parts of the datetime that you want. You want to get the datetime down to the minute, and then for each group with that "rounded-off" time, get the row with the maximum time.