MySQL date plus minus

Source: Internet
Author: User
Tags date1 month name

One, MySQL adds a time interval for the date: Date_add ().

1. Example:

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

2, MySQL adddate (), Addtime () function, can be replaced with Date_add ().

Second, MySQL is the date minus one time interval: Date_sub ():

1, 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

2, MySQL also has two functions subdate (), Subtime (), recommended, with Date_sub () to replace.

Third, 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).

1, 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

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

Four, 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.

V. Several functions of the current time:

1, now (); The current specific date and time,

2, Curdate (); Current date

3, Curtime (); Current time

4, date (date); Extract date part of date or date/time expression

5, Date_add (DATE, INTERVAL expr type); Adds a specified time interval to a date,

Expr table time interval, the type parameter can be the following value:

Microsecond SECOND MINUTE HOUR Day WEEK MONTH QUARTER Year

Second_microsecond Minute_microsecond Minute_second

Hour_microsecond Hour_second Hour_minute Day_microsecond

Day_second Day_minute Day_hour Year_month

6, Date_sub (DATE, INTERVAL expr type); The specified time interval is sent from the date.

The expr parameter is the interval you want to add, the type parameter is the same as Date_add ().

7, DATEDIFF (Date1, date2); Returns the number of days between two dates, with only the date portion of the value participating in the calculation.

8, Date_format (DATE, FORMAT); Displays the date/time in a different format.

format Specifies the date/time in which the output is formatted. The format is:

Format

Describe

Format

Describe

%a

Abbreviated Week name

%p

AM or PM

%b

Abbreviated month name

%r

Time, 12-hour (Hh:mm:ss AM or PM)

%c

Month, value

%s

Seconds (00-59)

%d

Day of the month with English prefixes

%s

Seconds (00-59)

%d

Day of the month, value (00-31)

%T

Time, 24-hour (HH:MM:SS)

%e

Day of the month, value (0-31)

%u

Week (00-53) Sunday is the first day of the week

%f

Microseconds

%u

Week (00-53) Monday is the first day of the week

%H

Hours (00-23)

%V

Week (01-53) Sunday is the first day of the week, with%x

%h

Hours (01-12)

%v

Week (01-53) Monday is the first day of the week, with%x

%I

Hours (01-12)

%W

Week name

%i

Minutes, value (00-59)

%w

Day of the week (0= Sunday, 6 = Saturday)

%j

Days of the Year (001-366)

%x

year, of which Sunday was the first day of the week, 4-bit, with%V used

%k

Hours (0-23)

%x

year, of which Monday was the first day of the week, 4-bit, with%v used

%l

Hours (1-12)

%Y

Year, 4 guests

%M

Month Name

%y

Year, 2 guests

%m

month, value (00-12)

Note:

UPDATE ' deadline ' SET ' end_time ' =date_add (' End_time ', Interval 1 month) where ' Function_type ' =1 plus one months

MySQL date plus minus

Related Article

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.