MySQL DATETIME DIFF 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6410642/
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 DATETIME DIFF query
提问by MartinJJ
I have a MySQL query that runs via cron every 30 minutes to delete old property listings, the query is:
我有一个 MySQL 查询，它每 30 分钟通过 cron 运行一次以删除旧的属性列表，查询是：
DELETE FROM $wpdb->posts WHERE post_type = 'rentals' AND DATEDIFF(NOW(), post_date_gmt) >=2
right now its in a testing phase and set to delete when the listing is 2 days old, this it does without a problem, the problem that i do have is that i need it to recognise the time from when the listing was posted and the time to when it should be deleted,
现在它处于测试阶段并设置为在列表发布 2 天后删除，这没有问题，我确实遇到的问题是我需要它来识别发布列表的时间和时间到什么时候应该删除，
Basically the table column of post_date_gmtis in the format of 2011-05-26 13:10:56and the column type is DATETIMEwhen i have the DATEDIFF (NOW()query running it needs to be equal to 48 hours or more from current time to delete the listing, this is not happening, the query just seems to say "this is the 2nd day, delete listing" so it gets deleted and this could be when just 24 and a half hours old, how can i make it go the full exact 48 hours?
基本上表列post_date_gmt的格式2011-05-26 13:10:56和列类型是DATETIME当我DATEDIFF (NOW()运行查询时它需要等于当前时间的 48 小时或更长时间才能删除列表，这不会发生，查询似乎只是说“这是第 2 天，删除列表”所以它被删除了，这可能是在 24 小时半的时候，我怎样才能让它完全精确到 48 小时？
Regards
问候
回答by VGE
Try timediff with a 48 hours delta instead . Or use a 3 days delta if precision is not an issue.
尝试使用 48 小时增量的 timediff 代替。或者，如果精度不是问题，则使用 3 天增量。
Try HOUR(TIMEDIFF(NOW(), date))
尝试 HOUR(TIMEDIFF(NOW(), date))
SELECT count(*) FROM $wpdb->posts WHERE post_type = 'rentals' AND HOUR(TIMEDIFF(NOW(), post_date_gmt)) >=48
There is a lot of MySQL date/time function : http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_hour
有很多 MySQL 日期/时间函数：http: //dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_hour
Note: use SELECt before trying DELETE
注意：在尝试 DELETE 之前使用 SELECT
回答by marco
you can DATEDIFF(NOW(), post_date_gmt) >= 3and it will be deleted 48 and half hours old
你可以DATEDIFF(NOW(), post_date_gmt) >= 3，它会在 48 个半小时后被删除
