в 13:46, 26 мая 2010

2010-05-26T13:46:00Z

Admin: 1 версия

2010-05-26T10:16:56Z

1 версия

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

==Calculation on Date data type==



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

</source>

==DATE_ADD() add a time interval to a given date==



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

</source>

==Date Calculations: calculate ages==



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

</source>

==Date Calculations: extract parts of dates==



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

</source>

==Date data type manipulation==



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

</source>

==Get Day of Month for a date==



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

</source>

==Get sub date==



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

</source>

==Get the YEAR part of the date==



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

</source>

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

SQL/MySQL/Date Time/Date Function - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия