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)

Mysql Date Manipulation

Mysql Date Manipulation

Mysql Date Manipulation

Tutorials