Query the data of the day before the current time interval in MySQL

1. Background

In actual projects, we will encounter the situation of distributed scheduled task execution. Sometimes when the scheduled task is executed, if the amount of queried data is relatively large, we will choose to execute the data filtered a few days ago before the execution time interval.

Avoid executing the full amount of data, which may cause excessive memory usage or long execution time, leading to OOM and other problems.

Here we will use SQL to filter queries with a time interval of 1 day or several days.

1.1. Query SQL statement

Here we use a talk_course table as the execution form of the query SQL statement. The fields of the table are as follows:

The table contains the following test data:

The SQL statement to query the day before the current execution time is as follows:

# Query data from 1 day before the current time SELECT * FROM `talk_course` t
WHERE t.createTime <= NOW() 
AND t.createTime >= date_sub(now(), interval 1 day);

Query results:

Query the time one day before the current time:

# Query the time of the day before the current time SELECT date_sub(now(),interval 1 day) as yesterday

1.2. Query statement analysis

Two functions are used here:

DATE_ADD(date,INTERVAL expr unit)
DATE_SUB(date,INTERVAL expr unit)

The DATE_ADD() function adds a specified time interval to a date.

The DATE_SUB() function subtracts a specified time interval from a date.

where the expr argument is the time interval you wish to add.

The type parameter can have the following values:

This is the end of this article about querying data one day before the current time interval in MySQL. For more relevant MySQL query data one day before the current time, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!