MySQL Date and Time Functions: addtime, adddate, date_add, subtime, subdate, date_sub

MySQL provides several functions to manipulate date and time values.

1. addtime() – adds a specified number of seconds to a date/time.

<code>select addtime(now(),1); -- add 1 second</code>

2. adddate() – adds days or an INTERVAL to a date.

<code>select adddate(now(),1); -- add 1 day</code>
<code>select adddate(now(), interval 1 day); -- add 1 day</code>
<code>select adddate(now(), interval 1 hour); -- add 1 hour</code>
<code>select adddate(now(), interval 1 minute); -- add 1 minute</code>
<code>select adddate(now(), interval 1 second); -- add 1 second</code>
<code>select adddate(now(), interval 1 microsecond); -- add 1 microsecond</code>
<code>select adddate(now(), interval 1 week); -- add 1 week</code>
<code>select adddate(now(), interval 1 month); -- add 1 month</code>
<code>select adddate(now(), interval 1 quarter); -- add 1 quarter</code>
<code>select adddate(now(), interval 1 year); -- add 1 year</code>

3. date_add() – adds an INTERVAL to a date, similar to adddate().

<code>select date_add(now(), interval 1 day); -- add 1 day</code>
<code>select date_add(now(), interval 1 hour); -- add 1 hour</code>
<code>select date_add(now(), interval 1 minute); -- add 1 minute</code>
<code>select date_add(now(), interval 1 second); -- add 1 second</code>
<code>select date_add(now(), interval 1 microsecond); -- add 1 microsecond</code>
<code>select date_add(now(), interval 1 week); -- add 1 week</code>
<code>select date_add(now(), interval 1 month); -- add 1 month</code>
<code>select date_add(now(), interval 1 quarter); -- add 1 quarter</code>
<code>select date_add(now(), interval 1 year); -- add 1 year</code>

4. subtime() – subtracts a specified number of seconds from a date/time.

<code>select subtime(now(),1); -- subtract 1 second</code>

5. subdate() – subtracts days or an INTERVAL from a date, mirroring adddate().

<code>select subdate(now(),1); -- subtract 1 day</code>
<code>select subdate(now(), interval 1 day); -- subtract 1 day</code>
<code>select subdate(now(), interval 1 hour); -- subtract 1 hour</code>
<code>select subdate(now(), interval 1 minute); -- subtract 1 minute</code>
<code>select subdate(now(), interval 1 second); -- subtract 1 second</code>
<code>select subdate(now(), interval 1 microsecond); -- subtract 1 microsecond</code>
<code>select subdate(now(), interval 1 week); -- subtract 1 week</code>
<code>select subdate(now(), interval 1 month); -- subtract 1 month</code>
<code>select subdate(now(), interval 1 quarter); -- subtract 1 quarter</code>
<code>select subdate(now(), interval 1 year); -- subtract 1 year</code>

6. date_sub() – subtracts an INTERVAL from a date, analogous to date_add().

<code>select date_sub(now(), interval 1 day); -- subtract 1 day</code>
<code>select date_sub(now(), interval 1 hour); -- subtract 1 hour</code>
<code>select date_sub(now(), interval 1 minute); -- subtract 1 minute</code>
<code>select date_sub(now(), interval 1 second); -- subtract 1 second</code>
<code>select date_sub(now(), interval 1 microsecond); -- subtract 1 microsecond</code>
<code>select date_sub(now(), interval 1 week); -- subtract 1 week</code>
<code>select date_sub(now(), interval 1 month); -- subtract 1 month</code>
<code>select date_sub(now(), interval 1 quarter); -- subtract 1 quarter</code>
<code>select date_sub(now(), interval 1 year); -- subtract 1 year</code>