SQL/MySQL/Date Time/Date Function

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>