Mysql Date Manipulation
Mysql Date Manipulation find out the difference between two values in a table.
Understand with Example
The Tutorial illustrate an example from Mysql Date Manipulation. To grasp this example, we create a table 'Person' with field name and data type. The createtable construct a table 'Person' with respective data type.
Query for creating table :-
mysql> Create table Person(name VARCHAR(20),birth DATE,death DATE); |
Output:-
Query for Inserting data into the table :-
Now Once the table 'person' is created, we insert the value into a table person. The insert into add the records or rows to the table 'Person'.
mysql> Insert into Person Values('ABC','1984-03-30',NULL); mysql> Insert into Person Values('CDE','1984-03-30','1999-05-31'); mysql> Insert into Person Values('FGH','1989-03-30','2009-05-13'); |
Query for viewing data from the table :-
mysql> select * from person; |
Output :-
+------+------------+------------+ | name | birth | death | +------+------------+------------+ | ABC | 1984-03-30 | NULL | | CDE | 1984-03-30 | 1999-05-31 | | FGH | 1989-03-30 | 2009-05-13 | +------+------------+------------+ 3 rows in set (0.00 sec) |
Query for finding age using the field death and birth from the table :-
The Query below find out the field difference in age between death and birth in year. The WHERE clause restrict the records from a table on the basis of condition specified where death is not null.
mysql> select name,birth,death, -> (year(death)-year(birth)) -> as age from person -> where death is not null; |
Output :-
+------+------------+------------+------+ | name | birth | death | age | +------+------------+------------+------+ | CDE | 1984-03-30 | 1999-05-31 | 15 | | FGH | 1989-03-30 | 2009-05-13 | 20 | +------+------------+------------+------+ 2 rows in set (0.00 sec) |