MySQL Tutorial/Date Time Functions/DATE SUB — различия между версиями

Текущая версия на 09:50, 26 мая 2010

Содержание

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)

Текущая версия на 09:50, 26 мая 2010 (просмотреть исходный код) Admin (обсуждение \| вклад) м (1 версия)	Текущая версия на 09:50, 26 мая 2010 (просмотреть исходный код) Admin (обсуждение \| вклад) м (1 версия)
(нет различий)