Share on Google+Share on Google+

Mysql Date Manipulation

Mysql Date Manipulation find out the difference between two values in a table.

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);


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)



Posted on: January 20, 2009 If you enjoyed this post then why not add us on Google+? Add us to your Circles

Share this Tutorial Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Discuss: Mysql Date Manipulation  

Post your Comment

Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image