MySQL Add and subtract time-function-time plus minus

Source: Internet
Author: User
Tags date1

1. MySQL adds a time interval for the date: Date_add ()
Set @dt = Now ();
Select Date_add (@dt, Interval 1 day); -Plus 1 days
Select Date_add (@dt, interval 1 hour); -Plus 1 hours
Select Date_add (@dt, interval 1 minute); -Plus 1 minutes
Select Date_add (@dt, Interval 1 second);-plus 1 seconds
Select Date_add (@dt, interval 1 microsecond);-plus 1 milliseconds
Select Date_add (@dt, Interval 1 week);-Plus 1 weeks
Select Date_add (@dt, interval 1 month);-Plus January
Select Date_add (@dt, interval 1 quarter);-Plus 1 seasons
Select Date_add (@dt, interval 1 year);-Plus 1 years
MySQL adddate (), Addtime () function, can be replaced with Date_add (). The following is an example of the date_add () implementation of the Addtime () feature:
mysql> Set @dt = ' 2009-09-09 12:12:33 ';
mysql>
mysql> Select Date_add (@dt, Interval ' 01:15:30 ' Hour_second);-plus 1 hours, 15 minutes, 30 seconds.
Date_add (@dt, Interval ' 01:15:30 ' Hour_second)

mysql> Select Date_add (@dt, Interval ' 1 01:15:30 ' Day_second);-Add 1 days, 1 hours, 15 minutes, 30 seconds.
Date_add (@dt, Interval ' 1 01:15:30 ' Day_second)
2008-08-10 13:28:03

The Date_add () function adds "1 hours, 15 minutes, 30 seconds" and "1 days, 1 hours, 15 minutes, 30 seconds" for @dt respectively.


2. MySQL for date minus one time interval: date_sub ()
mysql> Select Date_sub (' 1998-01-01 00:00:00 ', interval ' 1 1:1:1 ' Day_second);
date_sub (' 1998-01-01 00:00:00 ', interval ' 1 1:1:1 ' Day_second)
1997-12-30 22:58:59//www.forasp.cn finishing

MySQL date_sub () datetime functions and Date_add () are used in a consistent, no-repeat. In addition, MySQL also has two functions subdate (), Subtime (), recommended, with Date_sub () to replace.


3. MySQL Alternative Date function: Period_add (p,n), Period_diff (P1,P2)
The format of the function parameter "P" is "yyyymm" or "yymm", and the second parameter "n" means increment or subtract N month (month).
MySQL Period_add (p,n): Date plus/minus N months.
mysql> Select Period_add (200808,2), Period_add (20080808,-2)
| period_add (200808,2) | period_add (20080808,-2) |
Results |                200810 | 20080806 |
MySQL Period_diff (P1,P2): Date p1-p2, returns N months.
mysql> Select Period_diff (200808, 200801);
Period_diff (200808, 200801)
Results: 7

In MySQL, these two date functions are rarely used under normal circumstances.


4. MySQL date, time subtraction function: DateDiff (DATE1,DATE2), Timediff (time1,time2)

MySQL DateDiff (date1,date2): two date subtraction Date1 date2, returns the number of days.
Select DateDiff (' 2008-08-08 ', ' 2008-08-01 ');-7
Select DateDiff (' 2008-08-01 ', ' 2008-08-08 ');-7
MySQL Timediff (time1,time2): two date subtraction time1 time2, returns the time difference value.
Select Timediff (' 2008-08-08 08:08:08 ', ' 2008-08-08 00:00:00 ');-08:08:08
Select Timediff (' 08:08:08 ', ' 00:00:00 '); -08:08:08
Note: The Timediff (time1,time2) function must have the same two parameter types.

Copyright NOTICE: This article for Bo Master http://www.zuiniusn.com original article, without Bo Master permission not reproduced.

MySQL Add and subtract time-function-time plus minus

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.