SQL/MySQL/Date Time/Date Function — различия между версиями

Материал из SQL эксперт
Перейти к: навигация, поиск
м (1 версия)
 
(нет различий)

Версия 13:46, 26 мая 2010

Calculation on Date data type

/*
mysql> select * from Bird;
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| BlueBird | Joe   | Car     | f    | 1999-03-30 | NULL       |
| RedBird  | Yin   | Bus     | m    | 1979-04-30 | 0000-00-00 |
| RedBird  | Yin   | Bus     | m    | 1998-01-30 | NULL       |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)
mysql> SELECT name, birth FROM Bird
    -> WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;
Empty set (0.00 sec)

*/  
Drop table Bird;
CREATE TABLE Bird (
    name VARCHAR(20), 
    owner VARCHAR(20),
    species VARCHAR(20), 
    sex CHAR(1), 
    birth DATE, 
    death DATE
);
  
INSERT INTO  Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",1998-01-30);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL);
  
select * from Bird;
SELECT name, birth FROM Bird
WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;



DATE_ADD() add a time interval to a given date

/*
mysql> select * from Bird;
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| BlueBird | Joe   | Car     | f    | 1999-03-30 | NULL       |
| RedBird  | Yin   | Bus     | m    | 1979-04-30 | 0000-00-00 |
| RedBird  | Yin   | Bus     | m    | 1998-01-30 | NULL       |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)
mysql> SELECT name, birth FROM Bird
    -> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));
Empty set (0.00 sec)

*/  
Drop table Bird;
CREATE TABLE Bird (
    name VARCHAR(20), 
    owner VARCHAR(20),
    species VARCHAR(20), 
    sex CHAR(1), 
    birth DATE, 
    death DATE
);
  
INSERT INTO  Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",1998-01-30);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL);
  
select * from Bird;
 

SELECT name, birth FROM Bird
WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));



Date Calculations: calculate ages

/*
mysql> select * from Bird;
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| BlueBird | Joe   | Car     | f    | 1999-03-30 | NULL  |
| RedBird  | Yin   | Bus     | m    | 1979-04-30 | NULL  |
| RedBird  | Yin   | Bus     | m    | 1998-01-30 | NULL  |
+----------+-------+---------+------+------------+-------+
3 rows in set (0.00 sec)
mysql> /*how old each a Bird is*/
mysql> SELECT name, birth, CURDATE(),
    ->        (YEAR(CURDATE())-YEAR(birth))
    ->         - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
    ->        AS age
    ->        FROM Bird;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| BlueBird | 1999-03-30 | 2005-10-11 |    6 |
| RedBird  | 1979-04-30 | 2005-10-11 |   26 |
| RedBird  | 1998-01-30 | 2005-10-11 |    7 |
+----------+------------+------------+------+
3 rows in set (0.01 sec)

*/
Drop table Bird;
CREATE TABLE Bird (
    name VARCHAR(20), 
    owner VARCHAR(20),
    species VARCHAR(20), 
    sex CHAR(1), 
    birth DATE, 
    death DATE
);
  
INSERT INTO  Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",NULL);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL);
  
select * from Bird;
  

 
/*how old each a Bird is*/

SELECT name, birth, CURDATE(),
       (YEAR(CURDATE())-YEAR(birth))
        - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
       AS age
       FROM Bird;



Date Calculations: extract parts of dates

/*
mysql> select * from Bird;
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| BlueBird | Joe   | Car     | f    | 1999-03-30 | NULL  |
| RedBird  | Yin   | Bus     | m    | 1979-04-30 | NULL  |
| RedBird  | Yin   | Bus     | m    | 1998-01-30 | NULL  |
+----------+-------+---------+------+------------+-------+
3 rows in set (0.00 sec)
mysql> /*how old each a Bird is*/
mysql> SELECT name, birth, CURDATE(),
    ->        (YEAR(CURDATE())-YEAR(birth))
    ->         - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
    ->        AS age
    ->        FROM Bird;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| BlueBird | 1999-03-30 | 2005-10-11 |    6 |
| RedBird  | 1979-04-30 | 2005-10-11 |   26 |
| RedBird  | 1998-01-30 | 2005-10-11 |    7 |
+----------+------------+------------+------+
3 rows in set (0.00 sec)

*/  
Drop table Bird;
CREATE TABLE Bird (
    name VARCHAR(20), 
    owner VARCHAR(20),
    species VARCHAR(20), 
    sex CHAR(1), 
    birth DATE, 
    death DATE
);
  
INSERT INTO  Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",NULL);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL);
  
select * from Bird;
  
/*how old each a Bird is*/

SELECT name, birth, CURDATE(),
       (YEAR(CURDATE())-YEAR(birth))
        - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
       AS age
       FROM Bird;



Date data type manipulation

/*
mysql> select * from Bird;
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| BlueBird | Joe   | Car     | f    | 1999-03-30 | NULL       |
| RedBird  | Yin   | Bus     | m    | 1979-04-30 | 0000-00-00 |
| RedBird  | Yin   | Bus     | m    | 1998-01-30 | NULL       |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)
mysql> SELECT name, birth, death,
    ->        (YEAR(death)-YEAR(birth)) - (RIGHT(death,5)<RIGHT(birth,5))
    ->        AS age
    ->        FROM Bird WHERE death IS NOT NULL ORDER BY age;
+---------+------------+------------+-------+
| name    | birth      | death      | age   |
+---------+------------+------------+-------+
| RedBird | 1979-04-30 | 0000-00-00 | -1980 |
+---------+------------+------------+-------+
1 row in set (0.00 sec)

*/
Drop table Bird;
CREATE TABLE Bird (
    name VARCHAR(20), 
    owner VARCHAR(20),
    species VARCHAR(20), 
    sex CHAR(1), 
    birth DATE, 
    death DATE
);
  
INSERT INTO  Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",1998-01-30);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL);
  
select * from Bird;
  
SELECT name, birth, death,
       (YEAR(death)-YEAR(birth)) - (RIGHT(death,5)<RIGHT(birth,5))
       AS age
       FROM Bird WHERE death IS NOT NULL ORDER BY age;



Get Day of Month for a date

/*
mysql> select * from Bird;
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| BlueBird | Joe   | Car     | f    | 1999-03-30 | NULL       |
| RedBird  | Yin   | Bus     | m    | 1979-04-30 | 0000-00-00 |
| RedBird  | Yin   | Bus     | m    | 1998-01-30 | NULL       |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)
mysql> SELECT name, birth, DAYOFMONTH(birth) FROM Bird;
+----------+------------+-------------------+
| name     | birth      | DAYOFMONTH(birth) |
+----------+------------+-------------------+
| BlueBird | 1999-03-30 |                30 |
| RedBird  | 1979-04-30 |                30 |
| RedBird  | 1998-01-30 |                30 |
+----------+------------+-------------------+
3 rows in set (0.00 sec)

*/
Drop table Bird;
CREATE TABLE Bird (
    name VARCHAR(20), 
    owner VARCHAR(20),
    species VARCHAR(20), 
    sex CHAR(1), 
    birth DATE, 
    death DATE
);
  
INSERT INTO  Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",1998-01-30);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL);
  
select * from Bird;
 
SELECT name, birth, DAYOFMONTH(birth) FROM Bird;



Get sub date

/*
mysql> SELECT DATE_SUB(CURDATE(),INTERVAL 1 DAY);
+------------------------------------+
| DATE_SUB(CURDATE(),INTERVAL 1 DAY) |
+------------------------------------+
| 2005-10-08                         |
+------------------------------------+
1 row in set (0.00 sec)

*/  
SELECT DATE_SUB(CURDATE(),INTERVAL 1 DAY);



Get the YEAR part of the date

/*
mysql> select * from Bird;
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| BlueBird | Joe   | Car     | f    | 1999-03-30 | NULL       |
| RedBird  | Yin   | Bus     | m    | 1979-04-30 | 0000-00-00 |
| RedBird  | Yin   | Bus     | m    | 1998-01-30 | NULL       |
+----------+-------+---------+------+------------+------------+
3 rows in set (0.00 sec)
mysql> SELECT name, birth, YEAR(birth) FROM Bird;
+----------+------------+-------------+
| name     | birth      | YEAR(birth) |
+----------+------------+-------------+
| BlueBird | 1999-03-30 |        1999 |
| RedBird  | 1979-04-30 |        1979 |
| RedBird  | 1998-01-30 |        1998 |
+----------+------------+-------------+
3 rows in set (0.00 sec)

*/
Drop table Bird;
CREATE TABLE Bird (
    name VARCHAR(20), 
    owner VARCHAR(20),
    species VARCHAR(20), 
    sex CHAR(1), 
    birth DATE, 
    death DATE
);
  
INSERT INTO  Bird VALUES ("BlueBird","Joe","Car","f","1999-03-30",NULL);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1979-04-30",1998-01-30);
INSERT INTO  Bird VALUES ("RedBird","Yin","Bus","m","1998-01-30",NULL);
  
select * from Bird;
 
SELECT name, birth, YEAR(birth) FROM Bird;
Источник — «http://sqle.ru/index.php?title=SQL/MySQL/Date_Time/Date_Function&oldid=5320»