日期条件在24小时时间窗口中检索数据mysql [英] date condition to retrieve data in a 24 hour time window mysql

问题描述

我使用perl和DBI来查询mysql表。我需要在过去24小时内检索所有行（在3个不同的数据库中大约75,000行），理想情况下是在12:00 am到11:59 pm或00:00:00到23:59:59。

I am using perl and DBI to query a mysql table. I need to retrieve all rows (aprox. 75,000 rows from 3 separate databases) within the past 24 hours, ideally between 12:00 am and 11:59 pm or 00:00:00 and 23:59:59.

我使用 WHERE 日期条件，如下所示：

I was using a WHERE date condition like this:

SELECT * 
FROM table_name
WHERE insert_date >= DATE_SUB(NOW(), INTERVAL 1 DAY);

然后我将使用cron在午夜运行我的脚本。这工作得很好，但是由于在午夜定期大量的流量和查询的大小，cron安排的执行时间现在是凌晨3:00。我改变了我的sql，尝试从这样的偏移获得相同的24小时时间：

Then I would run my script at midnight using cron. This worked well enough, but due to a regular large volume of traffic at midnight and the size of the queries, the execution time scheduled with cron is now 3:00 am. I changed my sql to try and get the same 24 hour period from an offset like this:

SELECT * 
FROM table_name
WHERE insert_date 
BETWEEN DATE_SUB(DATE_SUB(NOW(), INTERVAL 3 HOUR), INTERVAL 1 DAY) 
AND DATE_SUB(NOW(), INTERVAL 3 HOUR);

这似乎工作正常为我的目的，但我想问，是有一个更可读和更准确的方式，使用mysql，从过去24小时（在00:00:00和23:59:59时间窗口之间）获取所有行，每天一次，同时从偏移时间运行查询？我一般都是新的，所以对我的整体方法的任何批评是非常欢迎。

This seems to work fine for my purposes but I want to ask, is there is a more readable and more accurate way, using mysql, to get all rows from the past 24 hours ( between 00:00:00 and 23:59:59 time window ) once a day while running the query from an offset time? I am generally new to all of this so any critiques on my overall approach are more than welcome.

推荐答案

我认为 insert_date 是 DATETIME ？

使用 BETWEEN 。我只需检查 DATE（insert_date）是昨天的日期。

It seems pointless to go to all the trouble of building two limits and using BETWEEN. I would simply check that DATE(insert_date) is yesterday's date. So

WHERE DATE(insert_date) = CURDATE() - INTERVAL 1 DAY

这篇关于日期条件在24小时时间窗口中检索数据mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！