1.需求:主表中create_time减去sync_time>10小时的传感器:
SELECT sensor_number,sync_time,create_time FROM `interface_data_list` WHERE
sync_time >= "2020-05-25 00:00:00" AND sync_time <= "2020-06-02 9:00:00"
AND TIMESTAMPDIFF(HOUR, sync_time, create_time) >= TIMESTAMPDIFF(HOUR, "2020-05-25 00:00:00", "2020-05-25 10:00:00")
GROUP BY sensor_number ORDER BY sync_time DESC
TIMESTAMPDIFF(HOUR, sync_time, create_time) > TIMESTAMPDIFF(HOUR, “2020-05-25 00:00:00”, “2020-05-25 10:00:00”)
首先第一步是create_time 减去 sync_time得到的数小时单位HOUR,所以我们后面的也要和前面的单位保持一致。
时间函数表:
2. date_add()函数
例子:
select date_add(now(), interval 3 day) ; //在当前时间上增加 3 天
select date_add(now(), interval -3 day) ; //在当前时间上减少 3 天