SQL/MySQL/Date Time/Date Function — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 10:16, 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;