MySQL MySQL查询删除时间戳早于当前时间戳的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10197633/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Query to delete rows whose timestamp is older than current timestamp
提问by Arihant
I am looking for a query that is able to delete all rows from a table in a database where timestamp is older than the current date/time or current timestamp.
我正在寻找一个查询，它能够从数据库中的表中删除所有行，其中时间戳早于当前日期/时间或当前时间戳。
Would really appreciate some help out here urgently!
真的很感谢这里的紧急帮助！
Here's the query I am using but as I thought it ain't working:
这是我正在使用的查询，但我认为它不起作用：
delete from events where timestamp<CURRENT_TIMESTAMP{);
回答by Dave Morton
Um... This may seem silly, but every record in the table will be older than Now(), since Now() is calculated at the time that query is processed. If you you want to delete a record that's older than another record, then you don't want to use Now(), but the timestamp from the record you're comparing the rest to. Or, if you want to delete records that are older than a specific point in time, then you need to calculate the timestamp that you want to use to compare against. For example, to delete records older than 10 minutes, you could use this:
嗯...这可能看起来很傻，但表中的每条记录都会比 Now() 旧，因为 Now() 是在处理查询时计算的。如果您想删除比另一条记录更旧的记录，那么您不想使用 Now()，而是要与其余记录进行比较的记录中的时间戳。或者，如果要删除早于特定时间点的记录，则需要计算要用于比较的时间戳。例如，要删除超过 10 分钟的记录，您可以使用以下命令：
DELETE FROM events WHERE timestamp < (NOW() - INTERVAL 10 MINUTE)
Or, for deleting records that are over a day old:
或者，要删除超过一天的记录：
DELETE FROM events WHERE timestamp < (NOW() - INTERVAL 1 DAY)
For specific points in time (e.g. Oct. 12th, 2012 at 4:15:00 PM GMT), there's a method to do that, but the syntax escapes me, right now. Where's my MySQL manual? :)
对于特定的时间点（例如，格林威治标准时间 2012 年 10 月 12 日下午 4:15:00），有一种方法可以做到这一点，但现在我无法理解语法。我的 MySQL 手册在哪里？:)
回答by ?ukasz Rysiak
delete from events where timestamp < NOW()
should be enough.
应该够了。
回答by Ranvijay Sachan
Hibernate (hql) Delete records older than 7 days
Hibernate (hql) 删除超过 7 天的记录
I am not sure, but you can Try this:
我不确定，但你可以试试这个：
String hqlQuery = "from PasswordHistory pwh "
+ "where pwh.created_date < datediff(curdate(), INTERVAL 7 DAY)";
List<Long> userList = (List<Long>)find(hqlQuery);
deleteAll(userList );// from baseDao
public void deleteAll(Collection list) {
getHibernateTemplate().deleteAll(list);
}
回答by Kris Robison
DELETE FROM events WHERE timestamp < UNIX_TIMESTAMP(NOW())
or if it's a standard datetime
或者如果它是标准日期时间
DELETE FROM events WHERE timestamp < NOW()
回答by Rajiv
DELETE FROM table WHERE date < '2011-09-21 08:21:22';
