Example analysis of interval calculation of mysql date and time

This article uses an example to describe the interval calculation of MySQL date and time. Share with you for your reference, the details are as follows:

First of all, we have to make it clear that MySQL interval values ​​are mainly used for date and time calculations. If we want to create interval values, we can use the following expression:

INTERVAL expr unit

Then, we need to understand that the INTERVAL keyword is the expr that determines the interval value, and the unit that specifies the interval unit. For example, to create a 1-day interval, we would use the following expression:

INTERVAL 1 DAY

Note, however, that INTERVAL and UNIT are not case-sensitive, so the following expression is equivalent to the above expression:

interval 1 day

The actual usage and syntax of interval values ​​for date and time arithmetic can be seen in the following code:

date + INTERVAL expr unit
date - INTERVAL expr unit

Interval values ​​are also used by various time functions such as DATE_ADD, DATE_SUB, TIMESTAMPADD, and TIMESTAMPDIFF. MySQL defines the standard format of expr and unit, as shown in the following table:

Let's take a look at an example. The following SQL statement adds 1 day to the date 2020-01-01 and returns the result as 2020-01-02:

mysql> SELECT '2020-01-01' + INTERVAL 1 DAY;
+-------------------------------+
| '2020-01-01' + INTERVAL 1 DAY |
+-------------------------------+
| 2020-01-02 |
+-------------------------------+
1 row in set (0.01 sec)

If we use an interval value in an expression involving DATE or DATETIME values ​​and the interval value is on the right side of the expression, we can use a negative value of expr as shown in the following example:

mysql> SELECT '2020-01-01' + INTERVAL -1 DAY;
+--------------------------------+
| '2020-01-01' + INTERVAL -1 DAY |
+--------------------------------+
| 2019-12-31 |
+--------------------------------+
1 row in set

Let's look at how to use DATE_ADD and DATE_SUB to add/subtract 1 month from a date value:

mysql> SELECT DATE_ADD('2020-01-01', INTERVAL 1 MONTH) 1_MONTH_LATER, 
    DATE_SUB('2020-01-01',INTERVAL 1 MONTH) 1_MONTH_BEFORE;
+---------------+----------------+
| 1_MONTH_LATER | 1_MONTH_BEFORE |
+---------------+----------------+
| 2020-02-01 | 2019-12-01 |
+---------------+----------------+
1 row in set

Let's look at a SQL statement that uses the TIMESTAMPADD(unit, interval, expression) function to add 30 minutes to a timestamp value:

mysql> SELECT TIMESTAMPADD(MINUTE,30,'2020-01-01') 30_MINUTES_LATER;
+---------------------+
| 30_MINUTES_LATER |
+---------------------+
| 2020-01-01 00:30:00 |
+---------------------+
1 row in set

After saying so much, you may still be a little confused. Don't worry, let's take a look at the specific operations. We create a new table called memberships for demonstration:

CREATE TABLE memberships (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(355) NOT NULL,
  plan VARCHAR(255) NOT NULL,
  expired_date DATE NOT NULL
);

In the memberships table above, the expired_date column stores the membership expiration date of each member. Now let's insert some data into the memberships table:

INSERT INTO memberships(email, plan, expired_date)
VALUES('[email protected]','Gold','2018-07-13'),
   ('[email protected]','Platinum','2018-07-10'),
   ('[email protected]','Silver','2018-07-15'),
   ('[email protected]','Gold','2018-07-20'),
   ('[email protected]','Silver','2018-07-08');

Now let's assume that today is 2018-07-06, and then we use SQL statements to query members whose membership will expire in the next 7 days:

SELECT 
  email,
  plan,
  expired_date,
  DATEDIFF(expired_date, '2018-07-06') remaining_days
FROM
  memberships
WHERE
  '2018-07-06' BETWEEN DATE_SUB(expired_date, INTERVAL 7 DAY) AND expired_date;

After executing the above query statement, the following results are obtained:

+--------------------------+----------+--------------+----------------+
| email | plan | expired_date | remaining_days |
+--------------------------+----------+--------------+----------------+
| [email protected] | Gold | 2018-07-13 | 7 |
| [email protected] | Platinum | 2018-07-10 | 4 |
| [email protected] | Silver | 2018-07-08 | 2 |
+--------------------------+----------+--------------+----------------+
3 rows in set

In the above SQL, the DATE_SUB function subtracts 7 days from the expiration date specified by the interval value (INTERVAL 7 DAY). That is to say, if the original date of a certain data is the 13th, minus seven days, it will be the 6th. This is roughly what it means. I believe everyone has a certain understanding of it.

That’s all for today.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.