MySQL – Query Records Between Yesterday and Today with Specific Time

dateMySQL

I seem to be having a case of the Fridays. I am new to MySQL (few weeks now) and I just can't seem to get a report automated. I need to be able to query data from yesterday 8 am until today 8 am. I am going to schedule the report to run at 10 am. What is the best way to do this? I have been running it manually with BETWEEN but this will not do. I tried to use the interval feature but no luck. I think I am almost there.

One try:

purchase_date BETWEEN '2020-11-18 09:00:00' and (DATE(NOW()) - INTERVAL 16 hour)

Another try:

purchase_date BETWEEN BETWEEN (DATE(NOW()) - INTERVAL 40 hour) and (DATE(NOW()) - INTERVAL 16 hour)

If you can just point me to the function or best option in this case, I should be able to figure out the arguments. Ideas/suggestions/thoughts? Thank you!

Best Answer

USE CURDATE() and INTERVAL

SELECT CURDATE() - INTERVAL 1 DAY + INTERVAL 8 HOUR, CURDATE() + INTERVAL 8 HOUR

RESULT

# CURDATE() - INTERVAL 1 DAY + INTERVAL 8 HOUR  CURDATE() + INTERVAL 8 HOUR
2020-11-19 08:00:00                             2020-11-20 08:00:00

Related Solutions

Mysql – How to improve the SQL statement with weekly results with week starting on Thursday or any other day of the week

Here is a query I wrote up to give you the most recent thursday and the ending wednesday

SELECT thuwk_beg + INTERVAL 0 second thu_beg,
thuwk_beg + INTERVAL 604799 second wed_end
FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A;

Here is an example for today, 2011-09-21

mysql> SELECT
    -> thuwk_beg + INTERVAL 0 second thu_beg,
    -> thuwk_beg + INTERVAL 604799 second wed_end
    -> FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
    -> FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
    -> FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A;
+---------------------+---------------------+
| thu_beg             | wed_end             |
+---------------------+---------------------+
| 2011-09-15 00:00:00 | 2011-09-21 23:59:59 |
+---------------------+---------------------+
1 row in set (0.00 sec)

Just replace the NOW() function calls with whatever datetime you like and you will have the week starting Thursday all the time for the give datetime you choose.

Here is another example using the specific date '2011-01-01'

mysql> SELECT
    -> thuwk_beg + INTERVAL 0 second thu_beg,
    -> thuwk_beg + INTERVAL 604799 second wed_end
    -> FROM (SELECT (DATE('2011-01-01') - INTERVAL daysbacktothursday DAY) thuwk_beg
    -> FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
    -> FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE('2011-01-01') dt) AAAA) AAA) AA) A;
+---------------------+---------------------+
| thu_beg             | wed_end             |
+---------------------+---------------------+
| 2010-12-30 00:00:00 | 2011-01-05 23:59:59 |
+---------------------+---------------------+
1 row in set (0.00 sec)

Your query of table referencing today would resemble something like this:

SELECT * from `table`,
(SELECT thuwk_beg + INTERVAL 0 second thu_beg,
thuwk_beg + INTERVAL 604799 second wed_end
FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A) M
WHERE `date` >= thu_beg
AND `date` <= wed_end;

Give it a Try !!!

UPDATE 2011-09-22 16:27 EDT

This was my proposed query for marking Thu-Wed.

SELECT thuwk_beg + INTERVAL 0 second thu_beg,
thuwk_beg + INTERVAL 604799 second wed_end
FROM (SELECT (DATE(NOW()) - INTERVAL daysbacktothursday DAY) thuwk_beg
FROM (SELECT SUBSTR('3456012',wkndx,1) daysbacktothursday
FROM (SELECT DAYOFWEEK(dt) wkndx FROM (SELECT DATE(NOW()) dt) AAAA) AAA) AA) A;

How about other weeks ???

(SELECT SUBSTR('6012345',wkndx,1) does the week starting Mon ending Sun
(SELECT SUBSTR('5601234',wkndx,1) does the week starting Tue ending Mon
(SELECT SUBSTR('4560123',wkndx,1) does the week starting Wed ending Tue
(SELECT SUBSTR('3456012',wkndx,1) does the week starting Thu ending Wed
(SELECT SUBSTR('2345601',wkndx,1) does the week starting Fri ending Thu
(SELECT SUBSTR('1234560',wkndx,1) does the week starting Sat ending Fri
(SELECT SUBSTR('0123456',wkndx,1) does the week starting Sun ending Sat

MySQL schema for recurring events – Query by specific date

Let's say you made the table like this

CREATE TABLE TrainEvents
(
    id INT NOT NULL AUTO_INCREMENT,
    station_arr_id INT NOT NULL,
    station_dep_id INT NOT NULL,
    arr_dt DATETIME NOT NULL,
    dep_dt DATETIME NOT NULL,

    ...
    PRIMARY KEY (id),
    KEY station_arr_index (station_arr_id,arr_dt),
    KEY station_dep_index (station_dep_id,dep_dt)
);

Next, you insert into the table the event

Arriving From Station 21 on April 30, 2014
Arrived at Station 29 at 10:00 AM on April 30, 2014
Departing From Station 29 1 minute later

Here is that INSERT query

INSERT INTO TrainEvents (station_arr_id,arr_dt,station_dep_id,dep_dt)
VALUES (21,'2014-04-30 10:00:00',29,'2014-04-30 10:01:00');

OK, now let's see how you can query the following departure

All Departures 10:00 AM on April 30, 2014 from Station 29
Check Departures 15 minutes before and after

Here is that SELECT query

SET @MinuteWindow = 15;
SET @DepartureDT = '2014-04-30 10:00:00';
SET @DepartureDTMinBef = @DepartureDT - INTERVAL @MinuteWindow MINUTE;
SET @DepartureDTMinAft = @DepartureDT + INTERVAL @MinuteWindow MINUTE;
SELECT * FROM TrainEvents
WHERE station_dep_id = 29
AND dep_dt >= @DepartureDTMinBef;
AND dep_dt <= @DepartureDTMinAft;

EPILOGUE

Given your initial idea, you could change the station_arr_id and station_dep_id into a single route_id and store the routes in a route table

CREATE TABLE TrainRoutes
(
    station_id INT NOT NULL AUTO_INCREMENT,
    station_name VARCHAR(128) NOT NULL,

    ...
    PRIMARY KEY (station_id),
    KEY station_name_ndx (station_name)
);
CREATE TABLE TrainEvents
(
    id INT NOT NULL AUTO_INCREMENT,
    route_id INT NOT NULL,
    arr_dt DATETIME NOT NULL,
    dep_dt DATETIME NOT NULL,

    ...
    PRIMARY KEY (id),
    KEY arr_index (route_id,arr_dt),
    KEY dep_index (route_id,dep_dt)
);

You could also make the event record arrivals and departures separately.

CREATE TABLE TrainRoutes
(
    station_id INT NOT NULL AUTO_INCREMENT,
    station_name VARCHAR(128) NOT NULL,

    ...
    PRIMARY KEY (station_id),
    KEY station_name_ndx (station_name)
);
CREATE TABLE TrainEvents
(
    id INT NOT NULL AUTO_INCREMENT,
    route_id INT NOT NULL,
    event_dt DATETIME NOT NULL,
    event_type TINYINT NOT NULL # 1 for arrival, 2 for departure

    ...
    PRIMARY KEY (id),
    KEY event_index1 (route_id,event_dt,event_type),
    KEY event_index2 (route_id,event_type,event_dt),
);

I am just giving ideas. I'll leave it to you to implement. Perhaps, to give you some UI ideas, see the site I use to commute : http://as0.mta.info/mnr/schedules/sched_form.cfm . The result of the route you pick in that site shows route departures within a 5-hour window (2.5 hours before and 2.5 hours after).