Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to calculate the date and time interval in mysql. The introduction in this article is very detailed and has certain reference value. Interested friends must read it!

The details are as follows:

Let's first make it clear that mysql interval values are mainly used for date and time calculations, and 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 expr, which 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 case-insensitive, so the following expression is equivalent to the above expression:

interval 1 day

For the actual usage and syntax of interval values for date and time arithmetic, we can look at the following code:

date + INTERVAL expr unitdate - INTERVAL expr unit

Interval values are also used for various time functions such as DATE_ADD, DATE_SUB, TIMESTAMPADD, and TIMESTAMPDIFF. mysql defines standard formats for expr and unit, as shown in the following table:

unit expression (expr) DAYDAYSDAY_HOUR 'DAYS HOURS' DAY_MICROSECOND 'DAYS HOURS: MINUTES: SECONDS. MICROSECONDS' DAY_MINUTE 'DAYS HOURS: MINUTES' DAY_SECOND 'DAYS HOURS: MINUTES: SECONDS' HOURSHOUR_MICROSECOND 'HOURS: MINUTES: SECONDS. MICROSECONDS' HOUR_MINUTE 'HOURS: MINUTES' HOUR_SECOND 'HOURS: MINUTES: SECONDS' MICROSECOND MINUTES: SECONDS. MICROSECONDS 'MINUTE_SECOND' MINUTES: SECONDS 'MONTHMONTHSQUARTERQUARTERSSECONDSECONDSSECOND_MICROSECOND' SECONDS. MICROSECONDS 'WEEK@EEKSYEARYEARSYEAR_MONTH' YEARS-MONTHS '

Let's look at an example. The following sql statement adds 1 day to the date 2020 - 01 - 01 and returns the result, 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 the 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 DATE_ADD and DATE_SUB can be used 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

Then we'll look at a sql that adds 30 minutes to the timestamp value using the TIMESTAMPADD (unit, interval, expression) function:

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

Having said that, you may still be a little confused. Let's look at the specific operation. 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 membership table above, the expired_date column stores the membership expiration date for each member, and then we insert some data into the membership table:

INSERT INTO memberships(email, plan, expired_date)VALUES('john.doe@yiibai.com','Gold','2018-07-13'), ('jane.minsu@yiibai.com','Platinum','2018-07-10'), ('david.corp@yiibai.com','Silver','2018-07-15'), ('julia.william@yiibai.com','Gold','2018-07-20'), ('peter.drucker@yiibai.com','Silver','2018-07-08');

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

SELECT email, plan, expired_date, DATEDIFF(expired_date, '2018-07-06') remaining_daysFROM membershipsWHERE '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 |+--------------------------+----------+--------------+----------------+| john.doe@yiibai.com | Gold | 2018-07-13 | 7 || jane.minsu@yiibai.com | Platinum | 2018-07-10 | 4 || peter.drucker@yiibai.com | Silver | 2018-07-08 | 2 |+--------------------------+----------+--------------+----------------+3 rows in set

In sql above, the DATE_SUB function subtracts 7 days from the expiration date specified by the interval value (INTERVAL 7 DAY), that is, the date of a certain piece of data is the 13th, minus 7 days, that is, the 6th. This is probably what I mean, I believe everyone has a certain understanding.

Above is "how to calculate the interval of date and time in mysql" all the contents of this article, thank you for reading! Hope to share the content to help everyone, more relevant knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.