How to SELECT all rows within a certain date/time range with a certain timestamp step size in MySQL?

问题: I have a table that contains sensor data with a column timestamp that holds the unix timestamp of the time the sensor measurement has been taken. Now I would like to SELECT...

问题:

I have a table that contains sensor data with a column timestamp that holds the unix timestamp of the time the sensor measurement has been taken. Now I would like to SELECT all measurements within a certain date/time range with a specific time step.
I figured the first part out myself like you can see in my posted code snippet below.

// With $date_start and $date_stop in the format: '2010-10-01 12:00:00'

$result = mysqli_query($connection, "SELECT sensor_1 
                                     FROM sensor_table 
                                     WHERE timestamp >= UNIX_TIMESTAMP($date_start) 
                                       AND timestamp < UNIX_TIMESTAMP($date_stop) 
                                     ORDER BY timestamp");  

Now is there a convenient way in MySQL to include a time step size into the same SELECT query?

My table contains thousands of measurements over months with one measurement taken every 5 seconds.

Now let's say I would like to SELECT measurements in between 2010-10-01 12:00:00 and 2010-10-02 12:00:00 but in this date/time range only SELECT one measurement every 10 minutes? (as my table contains measurements taken every 5 seconds).

Any smart ideas how to solve this in a single query?
(also other ideas are very welcome :))


回答1:

Since you take one measurement every 5 seconds, the difference between $date_start and the first matching measurement cannot be greater than 4. We then take one entry every 600 seconds (allowing for some discrepancy from clock to clock...)

SELECT sensor_1 
  FROM sensor_table
 WHERE timestamp >= UNIX_TIMESTAMP($date_start) 
       AND 
       timestamp < UNIX_TIMESTAMP($date_stop) 
       AND
       ((timestamp - UNIX_TIMESTAMP($date_start)) % 600) BETWEEN 0 AND 4 
 ORDER BY timestamp;

回答2:

It is not elegant, but you can do:

SELECT s.sensor_1 
FROM sensor_table s
WHERE s.timestamp >= UNIX_TIMESTAMP($date_start) AND
      s.timestamp < UNIX_TIMESTAMP($date_stop) AND
      s.timestamp = (SELECT MIN(s2.timestamp)
                     FROM sensor_table s2
                     WHERE s2.timestamp >= 60 * 10 * FLOOR(UNIX_TIMESTAMP(s.timestamp) / (60 * 10)) AND
                           s2.timestamp < s2.timestamp >= 60 * 10 * (1 + FLOOR(UNIX_TIMESTAMP(s.timestamp) / (60 * 10)))
                    )
ORDER BY timestamp;

This selects the first in each 10 minute period.


回答3:

I think that you could use a simple cursor in plSQL

    CREATE TABLE StoreValuesId
    (
      valueId int primary key;
    )

    CREATE OR REPLACE procedure_store[date_start date,date_stop date]
    DECLARE date_startUpdated date , date_stopUpdated date , date_diff TIME(7) = '00:10:00'
    IS 
    BEGIN
SELECT date_start INTO date_startUpdated;
SELECT date_stop INTO date_stopUpdated;
    IF timestamp BETWEEN date_start and date_stop then
    INSERT INTO StoreValuesId values(timestamp)

    date_startUpdated=DATEADD(SECOND, DATEDIFF(SECOND, 0, date_diff), date_startUpdated);
    date_stopUpdated=DATEADD(SECOND, DATEDIFF(SECOND, 0, date_diff), date_stopUpdated);
    END IF
    COMMIT;
    END

Then again the syntax might be wrong but I hope you'll get the idea (haven't played with sql in a while)

  • 发表于 2019-03-21 05:10
  • 阅读 ( 123 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除