SQL 日期时间查询

发布于 2024-11-15 15:35:26 字数 424 浏览 2 评论 0原文

您好，我尝试通过查询比较 MySQL 中的两个日期，一个是首次发布文章的日期，第二个日期是应该通过查询删除该文章的日期（365 天后，每 30 分钟由 cron 任务运行一次） .)，下面是我的 SQL 查询

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

我的日期以这种格式 2011-05-26 13:10:56 保存在数据库中，所以我的问题是 DATETIME 查询是否会遵循确切的日期 和时间或当 cron 在第 365 天运行时删除该文章，即使在其实际 365 天之前还剩下 13:10:56 ？

问候

原文

Hi im trying to compare two dates in MySQL via a query, one is a date when an article was first posted and the second date is for when it should be removed by the query (after 365 days, ran by a cron task every 30 minutes.), below is my SQL query

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

My date is held in the database in this format 2011-05-26 13:10:56 so my question is will the DATETIME query honour the exact date and time or when cron is ran at the 365th day delete the article even if there are still 13:10:56 left before it is actually 365 days old?

Regards

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

聆听风音 2024-11-22 15:35:26

正如所写，您的查询将删除所有超过 2 天的帖子（而不是 365 天）。

但是您的问题的答案很容易测试：

mysql> SELECT DATEDIFF('2011-01-01 00:10:00','2011-01-01 00:00:00');
+-------------------------------------------------------+
| DATEDIFF('2011-01-01 00:10:00','2011-01-01 00:00:00') |
+-------------------------------------------------------+
|                                                     0 | 
+-------------------------------------------------------+
1 row in set (0.00 sec)

如您所见，DATEDIFF() 仅返回整天，作为整数。使用“2011-01-01 23:59:59”作为第一个日期的类似测试也将产生 0。

因此，对您问题的简短回答是“是的，它尊重日期和时间，直至第二个”。

Your query, as written, will delete any posts older than 2 days--not 365.

But the answer to your question is easy to test:

mysql> SELECT DATEDIFF('2011-01-01 00:10:00','2011-01-01 00:00:00');
+-------------------------------------------------------+
| DATEDIFF('2011-01-01 00:10:00','2011-01-01 00:00:00') |
+-------------------------------------------------------+
|                                                     0 | 
+-------------------------------------------------------+
1 row in set (0.00 sec)

As you can see, DATEDIFF() returns only whole days, as an integer. A similar test with '2011-01-01 23:59:59' as the first date will also yield 0.

So the short answer to your question is "Yes, it honors the date and time, down to the second."

回复收藏 0 原文

~没有更多了~