MySQL DATETIME DIFF查询 [英] MySQL DATETIME DIFF query

问题描述

我有一个MySQL查询，每隔30分钟通过cron运行，以删除旧的资产列表，查询是：

I have a MySQL query that runs via cron every 30 minutes to delete old property listings, the query is:

 DELETE FROM $wpdb->posts WHERE post_type = 'rentals' AND DATEDIFF(NOW(), post_date_gmt) >=2  

现在它在一个测试阶段，并设置为删除时，列表是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,

基本上， post_date_gmt 的表列格式为的 2011-05-26 13:10:56 ，列类型为 DATETIME 当我有 DATEDIFF（NOW（）查询运行它需要等于48小时以上从当前时间删除列表，这没有发生，查询似乎就是说这是第二天，删除列表，所以它被删除，这可能是只有24个半的时间rs老，我怎么能让它完全精确的48小时？

Basically the table column of post_date_gmt is in the format of 2011-05-26 13:10:56 and the column type is DATETIME when 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?

注意

推荐答案

尝试使用48小时三角洲的timediff。
如果精度不是问题，请使用3天的增量。

Try timediff with a 48 hours delta instead . Or use a 3 days delta if precision is not an issue.

尝试 HOUR（TIMEDIFF（NOW（），date））

SELECT count(*) FROM $wpdb->posts WHERE post_type = 'rentals' AND HOUR(TIMEDIFF(NOW(),  post_date_gmt)) >=48

有很多MySQL日期/时间功能：
http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_hour

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

注意：在尝试DELETE

Note: use SELECt before trying DELETE

这篇关于MySQL DATETIME DIFF查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！