MySQL Tutorial/Date Time Functions/DATE SUB
Содержание
- 1 DATE_SUB(curdate(), INTERVAL 1.1 MINUTE_MICROSECOND);
- 2 DATE_SUB(curdate(), INTERVAL 1.1 MINUTE_SECOND);
- 3 DATE_SUB(curdate(), INTERVAL 1.1 SECOND);
- 4 DATE_SUB(curdate(), INTERVAL 1.1 SECOND_MICROSECOND);
- 5 DATE_SUB(curdate(), INTERVAL 1 DAY);
- 6 DATE_SUB(curdate(), INTERVAL 1 HOUR);
- 7 DATE_SUB(curdate(), INTERVAL 1 MICROSECOND);
- 8 DATE_SUB(curdate(), INTERVAL 1 MINUTE);
- 9 DATE_SUB(curdate(), INTERVAL 1 MONTH);
- 10 DATE_SUB(curdate(), INTERVAL 1 QUARTER);
- 11 DATE_SUB(curdate(), INTERVAL 1 SECOND);
- 12 DATE_SUB(curdate(), INTERVAL 1 WEEK);
- 13 DATE_SUB(curdate(), INTERVAL 1 YEAR);
- 14 DATE_SUB(date,INTERVAL expr unit)
- 15 Getting a date 6 months in the past
- 16 select DATE_SUB(curdate(), INTERVAL "1 1:1:1" DAY_SECOND);
- 17 select DATE_SUB(curdate(), INTERVAL "1 1:1" DAY_MINUTE);
- 18 select DATE_SUB(curdate(), INTERVAL "1:1:1" HOUR_SECOND);
- 19 select DATE_SUB(curdate(), INTERVAL "1 1" DAY_HOUR);
- 20 select DATE_SUB(curdate(), INTERVAL 1.1 DAY_MICROSECOND);
- 21 select DATE_SUB(curdate(), INTERVAL 1.1 HOUR_MICROSECOND);
- 22 select DATE_SUB(curdate(), INTERVAL "1:1" HOUR_MINUTE);
- 23 select DATE_SUB(curdate(), INTERVAL 1.1 HOUR_MINUTE); (with dot)
- 24 select DATE_SUB(curdate(), INTERVAL 1.1 HOUR_SECOND);
- 25 select DATE_SUB(curdate(), INTERVAL 1.1 SECOND_MICROSECOND);
- 26 select DATE_SUB(curdate(), INTERVAL "1-1" YEAR_MONTH);
- 27 select DATE_SUB(curdate(), INTERVAL "1.1" YEAR_MONTH); (with dot)
- 28 Selects all rows with a start_date value from within the last 30 days
- 29 Using DATE_SUB function to substract 55 years from current date
DATE_SUB(curdate(), INTERVAL 1.1 MINUTE_MICROSECOND);
mysql>
mysql>
mysql> select DATE_SUB(curdate(), INTERVAL 1.1 MINUTE_MICROSECOND);
+------------------------------------------------------+
| DATE_SUB(curdate(), INTERVAL 1.1 MINUTE_MICROSECOND) |
+------------------------------------------------------+
| 2007-07-21 23:59:58.999999 |
+------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
DATE_SUB(curdate(), INTERVAL 1.1 MINUTE_SECOND);
mysql>
mysql> select DATE_SUB(curdate(), INTERVAL 1.1 MINUTE_SECOND);
+-------------------------------------------------+
| DATE_SUB(curdate(), INTERVAL 1.1 MINUTE_SECOND) |
+-------------------------------------------------+
| 2007-07-21 23:58:59 |
+-------------------------------------------------+
1 row in set (0.00 sec)
DATE_SUB(curdate(), INTERVAL 1.1 SECOND);
mysql>
mysql> select DATE_SUB(curdate(), INTERVAL 1.1 SECOND);
+------------------------------------------+
| DATE_SUB(curdate(), INTERVAL 1.1 SECOND) |
+------------------------------------------+
| 2007-07-21 23:59:59 |
+------------------------------------------+
1 row in set (0.00 sec)
mysql>
DATE_SUB(curdate(), INTERVAL 1.1 SECOND_MICROSECOND);
mysql>
mysql> select DATE_SUB(curdate(), INTERVAL 1.1 SECOND_MICROSECOND);
+------------------------------------------------------+
| DATE_SUB(curdate(), INTERVAL 1.1 SECOND_MICROSECOND) |
+------------------------------------------------------+
| 2007-07-21 23:59:58.900000 |
+------------------------------------------------------+
1 row in set (0.00 sec)
DATE_SUB(curdate(), INTERVAL 1 DAY);
mysql>
mysql> select DATE_SUB(curdate(), INTERVAL 1 DAY);
+-------------------------------------+
| DATE_SUB(curdate(), INTERVAL 1 DAY) |
+-------------------------------------+
| 2007-07-21 |
+-------------------------------------+
1 row in set (0.00 sec)
mysql>
DATE_SUB(curdate(), INTERVAL 1 HOUR);
mysql>
mysql> select DATE_SUB(curdate(), INTERVAL 1 HOUR);
+--------------------------------------+
| DATE_SUB(curdate(), INTERVAL 1 HOUR) |
+--------------------------------------+
| 2007-07-21 23:00:00 |
+--------------------------------------+
1 row in set (0.00 sec)
mysql>
DATE_SUB(curdate(), INTERVAL 1 MICROSECOND);
mysql>
mysql> select DATE_SUB(curdate(), INTERVAL 1 MICROSECOND);
+---------------------------------------------+
| DATE_SUB(curdate(), INTERVAL 1 MICROSECOND) |
+---------------------------------------------+
| 2007-07-21 23:59:59.999999 |
+---------------------------------------------+
1 row in set (0.00 sec)
DATE_SUB(curdate(), INTERVAL 1 MINUTE);
mysql>
mysql> select DATE_SUB(curdate(), INTERVAL 1 MINUTE);
+----------------------------------------+
| DATE_SUB(curdate(), INTERVAL 1 MINUTE) |
+----------------------------------------+
| 2007-07-21 23:59:00 |
+----------------------------------------+
1 row in set (0.00 sec)
mysql>
DATE_SUB(curdate(), INTERVAL 1 MONTH);
mysql>
mysql> select DATE_SUB(curdate(), INTERVAL 1 MONTH);
+---------------------------------------+
| DATE_SUB(curdate(), INTERVAL 1 MONTH) |
+---------------------------------------+
| 2007-06-22 |
+---------------------------------------+
1 row in set (0.00 sec)
DATE_SUB(curdate(), INTERVAL 1 QUARTER);
mysql>
mysql> select DATE_SUB(curdate(), INTERVAL 1 QUARTER);
+-----------------------------------------+
| DATE_SUB(curdate(), INTERVAL 1 QUARTER) |
+-----------------------------------------+
| 2007-04-22 |
+-----------------------------------------+
1 row in set (0.00 sec)
DATE_SUB(curdate(), INTERVAL 1 SECOND);
mysql>
mysql> select DATE_SUB(curdate(), INTERVAL 1 SECOND);
+----------------------------------------+
| DATE_SUB(curdate(), INTERVAL 1 SECOND) |
+----------------------------------------+
| 2007-07-21 23:59:59 |
+----------------------------------------+
1 row in set (0.00 sec)
mysql>
DATE_SUB(curdate(), INTERVAL 1 WEEK);
mysql>
mysql> select DATE_SUB(curdate(), INTERVAL 1 WEEK);
+--------------------------------------+
| DATE_SUB(curdate(), INTERVAL 1 WEEK) |
+--------------------------------------+
| 2007-07-15 |
+--------------------------------------+
1 row in set (0.00 sec)
DATE_SUB(curdate(), INTERVAL 1 YEAR);
mysql>
mysql> select DATE_SUB(curdate(), INTERVAL 1 YEAR);
+--------------------------------------+
| DATE_SUB(curdate(), INTERVAL 1 YEAR) |
+--------------------------------------+
| 2006-07-22 |
+--------------------------------------+
1 row in set (0.00 sec)
DATE_SUB(date,INTERVAL expr unit)
expr is an expression specifying the interval value.
expr is a string.
expr may start with a "-" for negative intervals.
unit is a keyword indicating the units in which the expression should be interpreted.
The following table shows the expected form of the expr argument for each unit value.
unit Value Expected expr Format MICROSECOND MICROSECONDS SECOND SECONDS MINUTE MINUTES HOUR HOURS DAY DAYS WEEK WEEKS MONTH MONTHS QUARTER QUARTERS YEAR YEARS SECOND_MICROSECOND "SECONDS.MICROSECONDS" MINUTE_MICROSECOND "MINUTES.MICROSECONDS" MINUTE_SECOND "MINUTES:SECONDS" HOUR_MICROSECOND "HOURS.MICROSECONDS" HOUR_SECOND "HOURS:MINUTES:SECONDS" HOUR_MINUTE "HOURS:MINUTES" DAY_MICROSECOND "DAYS.MICROSECONDS" DAY_SECOND "DAYS HOURS:MINUTES:SECONDS" DAY_MINUTE "DAYS HOURS:MINUTES" DAY_HOUR "DAYS HOURS" YEAR_MONTH "YEARS-MONTHS"
mysql>
mysql> select DATE_SUB(curdate(), INTERVAL 1 DAY);
+-------------------------------------+
| DATE_SUB(curdate(), INTERVAL 1 DAY) |
+-------------------------------------+
| 2007-07-21 |
+-------------------------------------+
1 row in set (0.00 sec)
mysql>
Getting a date 6 months in the past
mysql>
mysql>
mysql> SELECT DATE_SUB(CURDATE(), INTERVAL 6 MONTH);
+---------------------------------------+
| DATE_SUB(CURDATE(), INTERVAL 6 MONTH) |
+---------------------------------------+
| 2007-01-21 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql>
select DATE_SUB(curdate(), INTERVAL "1 1:1:1" DAY_SECOND);
mysql>
mysql> select DATE_SUB(curdate(), INTERVAL "1 1:1:1" DAY_SECOND);
+----------------------------------------------------+
| DATE_SUB(curdate(), INTERVAL "1 1:1:1" DAY_SECOND) |
+----------------------------------------------------+
| 2007-07-20 22:58:59 |
+----------------------------------------------------+
1 row in set (0.00 sec)
mysql>
select DATE_SUB(curdate(), INTERVAL "1 1:1" DAY_MINUTE);
mysql>
mysql> select DATE_SUB(curdate(), INTERVAL "1 1:1" DAY_MINUTE);
+--------------------------------------------------+
| DATE_SUB(curdate(), INTERVAL "1 1:1" DAY_MINUTE) |
+--------------------------------------------------+
| 2007-07-20 22:59:00 |
+--------------------------------------------------+
1 row in set (0.00 sec)
mysql>
select DATE_SUB(curdate(), INTERVAL "1:1:1" HOUR_SECOND);
mysql>
mysql> select DATE_SUB(curdate(), INTERVAL "1:1:1" HOUR_SECOND);
+---------------------------------------------------+
| DATE_SUB(curdate(), INTERVAL "1:1:1" HOUR_SECOND) |
+---------------------------------------------------+
| 2007-07-21 22:58:59 |
+---------------------------------------------------+
1 row in set (0.00 sec)
mysql>
select DATE_SUB(curdate(), INTERVAL "1 1" DAY_HOUR);
mysql>
mysql> select DATE_SUB(curdate(), INTERVAL "1 1" DAY_HOUR);
+----------------------------------------------+
| DATE_SUB(curdate(), INTERVAL "1 1" DAY_HOUR) |
+----------------------------------------------+
| 2007-07-20 23:00:00 |
+----------------------------------------------+
1 row in set (0.00 sec)
mysql>
select DATE_SUB(curdate(), INTERVAL 1.1 DAY_MICROSECOND);
mysql>
mysql> select DATE_SUB(curdate(), INTERVAL 1.1 DAY_MICROSECOND);
+---------------------------------------------------+
| DATE_SUB(curdate(), INTERVAL 1.1 DAY_MICROSECOND) |
+---------------------------------------------------+
| 2007-07-21 23:59:58.999999 |
+---------------------------------------------------+
1 row in set (0.00 sec)
mysql>
select DATE_SUB(curdate(), INTERVAL 1.1 HOUR_MICROSECOND);
mysql>
mysql> select DATE_SUB(curdate(), INTERVAL 1.1 HOUR_MICROSECOND);
+----------------------------------------------------+
| DATE_SUB(curdate(), INTERVAL 1.1 HOUR_MICROSECOND) |
+----------------------------------------------------+
| 2007-07-21 23:59:58.999999 |
+----------------------------------------------------+
1 row in set (0.00 sec)
mysql>
select DATE_SUB(curdate(), INTERVAL "1:1" HOUR_MINUTE);
mysql>
mysql> select DATE_SUB(curdate(), INTERVAL "1:1" HOUR_MINUTE);
+-------------------------------------------------+
| DATE_SUB(curdate(), INTERVAL "1:1" HOUR_MINUTE) |
+-------------------------------------------------+
| 2007-07-21 22:59:00 |
+-------------------------------------------------+
1 row in set (0.00 sec)
mysql>
select DATE_SUB(curdate(), INTERVAL 1.1 HOUR_MINUTE); (with dot)
mysql>
mysql>
mysql>
mysql> select DATE_SUB(curdate(), INTERVAL 1.1 HOUR_MINUTE);
+-----------------------------------------------+
| DATE_SUB(curdate(), INTERVAL 1.1 HOUR_MINUTE) |
+-----------------------------------------------+
| 2007-07-21 22:59:00 |
+-----------------------------------------------+
1 row in set (0.00 sec)
select DATE_SUB(curdate(), INTERVAL 1.1 HOUR_SECOND);
mysql>
mysql> select DATE_SUB(curdate(), INTERVAL 1.1 HOUR_SECOND);
+-----------------------------------------------+
| DATE_SUB(curdate(), INTERVAL 1.1 HOUR_SECOND) |
+-----------------------------------------------+
| 2007-07-21 23:58:59 |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql>
select DATE_SUB(curdate(), INTERVAL 1.1 SECOND_MICROSECOND);
mysql>
mysql>
mysql> select DATE_SUB(curdate(), INTERVAL 1.1 SECOND_MICROSECOND);
+------------------------------------------------------+
| DATE_SUB(curdate(), INTERVAL 1.1 SECOND_MICROSECOND) |
+------------------------------------------------------+
| 2007-07-21 23:59:58.900000 |
+------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
select DATE_SUB(curdate(), INTERVAL "1-1" YEAR_MONTH);
mysql>
mysql> select DATE_SUB(curdate(), INTERVAL "1-1" YEAR_MONTH);
+------------------------------------------------+
| DATE_SUB(curdate(), INTERVAL "1-1" YEAR_MONTH) |
+------------------------------------------------+
| 2006-06-22 |
+------------------------------------------------+
1 row in set (0.00 sec)
mysql>
select DATE_SUB(curdate(), INTERVAL "1.1" YEAR_MONTH); (with dot)
mysql>
mysql> select DATE_SUB(curdate(), INTERVAL "1.1" YEAR_MONTH);
+------------------------------------------------+
| DATE_SUB(curdate(), INTERVAL "1.1" YEAR_MONTH) |
+------------------------------------------------+
| 2006-06-22 |
+------------------------------------------------+
1 row in set (0.00 sec)
mysql>
Selects all rows with a start_date value from within the last 30 days
mysql>
mysql>
mysql>
mysql> CREATE TABLE Employee(
-> id int,
-> first_name VARCHAR(15),
-> last_name VARCHAR(15),
-> start_date DATE,
-> end_date DATE,
-> salary FLOAT(8,2),
-> city VARCHAR(10),
-> description VARCHAR(15)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values (1,"Jason", "Martin", "19960725", "20060725", 1234.56, "Toronto", "Programmer");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(2,"Alison", "Mathews", "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(3,"James", "Smith", "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(4,"Celia", "Rice", "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(5,"Robert", "Black", "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(6,"Linda", "Green", "20070730", "19960104", 4322.78,"New York", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(7,"David", "Larry", "19901231", "19980212", 7897.78,"New York", "Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(8,"James", "Cat", "19960917", "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id | first_name | last_name | start_date | end_date | salary | city | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 2007-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql>
mysql> SELECT * FROM employee
-> WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= start_date;
+------+------------+-----------+------------+------------+---------+----------+-------------+
| id | first_name | last_name | start_date | end_date | salary | city | description |
+------+------------+-----------+------------+------------+---------+----------+-------------+
| 6 | Linda | Green | 2007-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
+------+------------+-----------+------------+------------+---------+----------+-------------+
1 row in set (0.00 sec)
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql>
Using DATE_SUB function to substract 55 years from current date
mysql>
mysql> CREATE TABLE Employee(
-> id int,
-> first_name VARCHAR(15),
-> last_name VARCHAR(15),
-> start_date DATE,
-> end_date DATE,
-> salary FLOAT(8,2),
-> city VARCHAR(10),
-> description VARCHAR(15)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values (1,"Jason", "Martin", "19960725", "20060725", 1234.56, "Toronto", "Programmer");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(2,"Alison", "Mathews", "19760321", "19860221", 6661.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(3,"James", "Smith", "19781212", "19900315", 6544.78, "Vancouver","Tester");
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(4,"Celia", "Rice", "19821024", "19990421", 2344.78, "Vancouver","Manager");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(5,"Robert", "Black", "19840115", "19980808", 2334.78, "Vancouver","Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(6,"Linda", "Green", "19870730", "19960104", 4322.78,"New York", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(7,"David", "Larry", "19901231", "19980212", 7897.78,"New York", "Manager");
Query OK, 1 row affected (0.02 sec)
mysql>
mysql> insert into Employee(id,first_name, last_name, start_date, end_Date, salary, City, Description)
-> values(8,"James", "Cat", "19960917", "20020415", 1232.78,"Vancouver", "Tester");
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> select * from Employee;
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| id | first_name | last_name | start_date | end_date | salary | city | description |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25 | 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21 | 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15 | 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21 | 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08 | 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04 | 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12 | 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15 | 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+------------+---------+-----------+-------------+
8 rows in set (0.00 sec)
mysql>
mysql>
mysql> SELECT *
-> FROM employee
-> WHERE start_date < DATE_SUB(curdate(),interval 55 year);
Empty set (0.01 sec)
mysql>
mysql>
mysql> drop table Employee;
Query OK, 0 rows affected (0.00 sec)