Date_add function to calculate date & time
date_add function is used to do various calculations on date & times field of MySQL table. By using current date function we can calculate future date or time by adding day, month, year , hour, seconds etc. The calculated date value can be stored in a record. We can selectively update any date and time field by using SQL where clause in our query. We can apply this date_add function for time calculation also. The output of the date_add function we can format to display the output in the way we want.
Here is the basic syntax of the date_add function.
date_add(date_field, interval expr unit);
Updating one field data by adding date ( period ) with another field value
We can update one field of a table by adding some date value to another field ( or column ) of the table. We will use one where clause to restrict the updating to one record only, however you can use as per your requirement and exclude the where clause.
Let us start by adding 3 month to our dt field and updating the same to dt2 field ( Download the structure and data for dt_tb table at the end)
update dt_tb set dt2=date_add(dt,interval 3 month) where id = 10
When we run this query the value of dt2 field for the record with id =10 will become 2007-04-22. This value has come from the value of dt field ( 2007-01-22) by adding 3 months. Same way let us try by adding 5 years to the dt field and updating the dt2 field
update dt_tb set dt2=date_add(now(),interval 5 year) where id = 10
Same way let us add 300 days
update dt_tb set dt2=date_add(dt,interval 300 day) where id = 10
We can add week and quarter to the date value by using date_add function. Here is the sample query.
update dt_tb set dt2=date_add(dt,interval 3 week) where id = 10
We have used one update query as an example but this can be any other query type.
update dt_tb set dt2=date_add(dt,interval 3 quarter) where id = 10
Adding day month & year to current date and storing in field.
Some time we will be adding dates to the current date & time and storing them in a field. Best example is the book return date of a library. From the issue date ( today's date ) we can add 15 days and use that as return date. Here is an example
select date_add(now(),interval 15 day) as return_date
The above query will display date & time by adding 15 days to current date and time.
We can use one insert command to add a record by storing present date & time in one field and return date ( by adding 15 days ) in another field.
insert into dt_tb(dt,dt2) values(now(), date_add(now(),interval 15 day) )
The above query will add one record with issue date or current date and another field with return date by adding 15 days to current date.
We can use similar query for fixed deposit maturity date by adding interval to current date in a bank application.
Adding time interval to a field data
By using date_add function we are not only adding day, month, year etc we can also add hour, minutes, seconds to the record. Here is an example of adding hour
SELECT dt, date_add( dt, INTERVAL 5 HOUR ) FROM dt_tb
Above query will display dt field values in one column and one more column by adding 5 hours to the time value of dt field. We can add various date formats to above query to display data based on our requirement. We will modify the above code to display only time part of the date data by using date_format function.
SELECT dt, date_format(date_add( dt, INTERVAL 5 HOUR ), '%T')FROM dt_tb
The above query can be modified to display other types of date formats.
We have used different types queries with its combinations in a date_add function to explore different requirements.
table structure and data for dt_tb table
CREATE TABLE `dt_tb` (
`id` int(2) NOT NULL auto_increment,
`dt` datetime NOT NULL default '0000-00-00 00:00:00',
`dt2` datetime NOT NULL default '0000-00-00 00:00:00',
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;
-- Dumping data for table `dt_tb`
INSERT INTO `dt_tb` VALUES (1, '2007-02-15 00:00:00', '2005-01-25 00:00:00');
INSERT INTO `dt_tb` VALUES (2, '2007-02-12 23:56:54', '2005-06-12 00:00:00');
INSERT INTO `dt_tb` VALUES (10, '2007-01-22 00:00:00', '2007-04-22 00:00:00');
INSERT INTO `dt_tb` VALUES (11, '2009-03-15 07:56:14', '2009-03-30 07:56:14');
Be the first to post comment on this article :
|Date & Time functions in SQL|