в 13:44, 26 мая 2010

2010-05-26T13:44:56Z

Admin: 1 версия

2010-05-26T09:50:40Z

1 версия

Новая страница

== DATE_SUB(curdate(), INTERVAL 1.1 MINUTE_MICROSECOND);==



<source lang="sql">
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></source>

== DATE_SUB(curdate(), INTERVAL 1.1 MINUTE_SECOND);==



<source lang="sql">
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)</source>

== DATE_SUB(curdate(), INTERVAL 1.1 SECOND);==



<source lang="sql">
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></source>

== DATE_SUB(curdate(), INTERVAL 1.1 SECOND_MICROSECOND);==



<source lang="sql">
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)</source>

== DATE_SUB(curdate(), INTERVAL 1 DAY);==



<source lang="sql">
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></source>

== DATE_SUB(curdate(), INTERVAL 1 HOUR);==



<source lang="sql">
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></source>

== DATE_SUB(curdate(), INTERVAL 1 MICROSECOND);==



<source lang="sql">
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)</source>

== DATE_SUB(curdate(), INTERVAL 1 MINUTE);==



<source lang="sql">
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></source>

== DATE_SUB(curdate(), INTERVAL 1 MONTH);==



<source lang="sql">
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)</source>

== DATE_SUB(curdate(), INTERVAL 1 QUARTER);==



<source lang="sql">
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)</source>

== DATE_SUB(curdate(), INTERVAL 1 SECOND);==



<source lang="sql">
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></source>

== DATE_SUB(curdate(), INTERVAL 1 WEEK);==



<source lang="sql">
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)</source>

== DATE_SUB(curdate(), INTERVAL 1 YEAR);==



<source lang="sql">
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)</source>

== 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"



<source lang="sql">
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></source>

== Getting a date 6 months in the past==



<source lang="sql">
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></source>

== select DATE_SUB(curdate(), INTERVAL "1 1:1:1" DAY_SECOND);==



<source lang="sql">
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></source>

== select DATE_SUB(curdate(), INTERVAL "1 1:1" DAY_MINUTE);==



<source lang="sql">
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></source>

== select DATE_SUB(curdate(), INTERVAL "1:1:1" HOUR_SECOND);==



<source lang="sql">
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></source>

== select DATE_SUB(curdate(), INTERVAL "1 1" DAY_HOUR);==



<source lang="sql">
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></source>

== select DATE_SUB(curdate(), INTERVAL 1.1 DAY_MICROSECOND);==



<source lang="sql">
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></source>

== select DATE_SUB(curdate(), INTERVAL 1.1 HOUR_MICROSECOND);==



<source lang="sql">
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></source>

== select DATE_SUB(curdate(), INTERVAL "1:1" HOUR_MINUTE);==



<source lang="sql">
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></source>

== select DATE_SUB(curdate(), INTERVAL 1.1 HOUR_MINUTE); (with dot)==



<source lang="sql">
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)</source>

== select DATE_SUB(curdate(), INTERVAL 1.1 HOUR_SECOND);==



<source lang="sql">
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></source>

== select DATE_SUB(curdate(), INTERVAL 1.1 SECOND_MICROSECOND);==



<source lang="sql">
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></source>

== select DATE_SUB(curdate(), INTERVAL "1-1" YEAR_MONTH);==



<source lang="sql">
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></source>

== select DATE_SUB(curdate(), INTERVAL "1.1" YEAR_MONTH); (with dot)==



<source lang="sql">
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></source>

== Selects all rows with a start_date value from within the last 30 days==



<source lang="sql">
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></source>

== Using DATE_SUB function to substract 55 years from current date==



<source lang="sql">
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)</source>

← Предыдущая	Версия 13:44, 26 мая 2010
(нет различий)

MySQL Tutorial/Date Time Functions/DATE SUB - История изменений

в 13:44, 26 мая 2010

Admin: 1 версия