一个 mysql的sql 需要删除 staytime字段(类型 时间) 与现在 时间 相差5分的记录
now()-staytime > 5 minute
我开始 选用的是TIMEDIFF 但是发现无法 应用 》5 分这个条件
例如
> SELECT TIMEDIFF('2000:01:01 00:00:00', -> '2000:01:01 00:00:00.000001'); -> '-00:00:00.000001'
这里不知道哦啊如何表达 》 5分的条件
后来我用
staytime< subdate(now, INTERVAL 5 MINUTE);
可以 完成
想知道TIMEDIFF可以做吗 ? 或者还有其他方法?
SQL code
mysql> SELECT TIMEDIFF('2010-10-10 10:10:00','2010-10-10 10:15:00');
+-------------------------------------------------------+
| TIMEDIFF('2010-10-10 10:10:00','2010-10-10 10:15:00') |
+-------------------------------------------------------+
| -00:05:00 |
+-------------------------------------------------------+
1 row in set (0.05 sec)
mysql> SELECT TIMEDIFF('2010-10-10 10:20:00','2010-10-10 10:15:00');
+-------------------------------------------------------+
| TIMEDIFF('2010-10-10 10:20:00','2010-10-10 10:15:00') |
+-------------------------------------------------------+
| 00:05:00 |
+-------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
回答者:zuoxingyu - 2010-10-14 18:23:31
SQL code
mysql> SELECT minute(TIMEDIFF('2010-10-10 10:20:00','2010-10-10 10:15:00'));
+---------------------------------------------------------------+
| minute(TIMEDIFF('2010-10-10 10:20:00','2010-10-10 10:15:00')) |
+---------------------------------------------------------------+
| 5 |
+---------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
回答者:zuoxingyu - 2010-10-14 18:24:37
where staytime<now()-interval 5 minute
应该如上这样,这样的话,还可以使用staytime上的索引。
回答者:ACMAIN_CHM - 2010-10-14 18:57:18
感谢楼上 where staytime<now()-interval 5 minute 这个按照道理是 最好
回答
now()-staytime > 5 minute
我开始 选用的是TIMEDIFF 但是发现无法 应用 》5 分这个条件
例如
> SELECT TIMEDIFF('2000:01:01 00:00:00', -> '2000:01:01 00:00:00.000001'); -> '-00:00:00.000001'
这里不知道哦啊如何表达 》 5分的条件
后来我用
staytime< subdate(now, INTERVAL 5 MINUTE);
可以 完成
想知道TIMEDIFF可以做吗 ? 或者还有其他方法?
SQL code
mysql> SELECT TIMEDIFF('2010-10-10 10:10:00','2010-10-10 10:15:00');
+-------------------------------------------------------+
| TIMEDIFF('2010-10-10 10:10:00','2010-10-10 10:15:00') |
+-------------------------------------------------------+
| -00:05:00 |
+-------------------------------------------------------+
1 row in set (0.05 sec)
mysql> SELECT TIMEDIFF('2010-10-10 10:20:00','2010-10-10 10:15:00');
+-------------------------------------------------------+
| TIMEDIFF('2010-10-10 10:20:00','2010-10-10 10:15:00') |
+-------------------------------------------------------+
| 00:05:00 |
+-------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
回答者:zuoxingyu - 2010-10-14 18:23:31
SQL code
mysql> SELECT minute(TIMEDIFF('2010-10-10 10:20:00','2010-10-10 10:15:00'));
+---------------------------------------------------------------+
| minute(TIMEDIFF('2010-10-10 10:20:00','2010-10-10 10:15:00')) |
+---------------------------------------------------------------+
| 5 |
+---------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
回答者:zuoxingyu - 2010-10-14 18:24:37
where staytime<now()-interval 5 minute
应该如上这样,这样的话,还可以使用staytime上的索引。
回答者:ACMAIN_CHM - 2010-10-14 18:57:18
感谢楼上 where staytime<now()-interval 5 minute 这个按照道理是 最好
回答