BryanK BryanK - 7 months ago 17
Perl Question

date condition to retrieve data in a 24 hour time window mysql

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.

I was using a

WHERE
date condition like this:

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


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);


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.

Answer

I presume insert_date is a DATETIME?

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