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)
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
