Mysql's Data_add function and date format function Description _mysql

Source: Internet
Author: User
Tags datetime month name time interval
    • Date_add (date, INTERVAL expr type) date_sub (date, INTERVAL expr type )

These functions perform date operations. Date is a DATETIME or date value that specifies the starting time.  expr is an expression that specifies the value of a time interval that is added or subtracted from the start date. Expr is a string; For a negative time interval, it can begin with a '-'. type is the keyword, which indicates how the expression is interpreted.

Keyword Interva and type classifiers are case-insensitive.

The following table shows the relationship between the type and the expr parameters:

type value

expected to Expr format

Microsecond

microseconds

SECOND

SECONDS

MINUTE

MINUTES

HOUR

HOURS

Day

Days

WEEK

WEEKS

MONTH

MONTHS

Quarter

Quarters

Year

YEARS

Second_microsecond

' SECONDS. Microseconds '

Minute_microsecond

' MINUTES. Microseconds '

Minute_second

' Minutes:seconds '

Hour_microsecond

' HOURS. Microseconds '

Hour_second

' HOURS:MINUTES:SECONDS '

Hour_minute

' Hours:minutes '

Day_microsecond

' Days. Microseconds '

Day_second

' Days HOURS:MINUTES:SECONDS '

Day_minute

' Days Hours:minutes '

Day_hour

' Days HOURS '

Year_month

' Years-months '

MySQL allows punctuation separators in any expr format. The suggested separator is shown in the table. If the date parameter is a date value and your calculation only includes the year, month, and day portions (that is, there is no time part), the result is a date value. Otherwise, the result will be a datetime value.

If the expression at the other end is a date or datetime value, the interval expr type is only allowed at both ends of the + operator. For the-operator, the INTERVAL expr type is only allowed at the right end because it is meaningless to extract a date or datetime value from one time interval. (see the example below).

mysql> SELECT ' 1997-12-31 23:59:59 ' + INTERVAL 1 SECOND;

-> ' 1998-01-01 00:00:00 '

mysql> SELECT INTERVAL 1 day + ' 1997-12-31 ';

-> ' 1998-01-01 '

mysql> SELECT ' 1998-01-01 '-INTERVAL 1 SECOND;

-> ' 1997-12-31 23:59:59 '

Mysql> SELECT date_add (' 1997-12-31 23:59:59 ',

-> INTERVAL 1 SECOND);

-> ' 1998-01-01 00:00:00 '

Mysql> SELECT date_add (' 1997-12-31 23:59:59 ',

-> INTERVAL 1 day);

-> ' 1998-01-01 23:59:59 '

Mysql> SELECT date_add (' 1997-12-31 23:59:59 ',

-> INTERVAL ' 1:1 ' minute_second);

-> ' 1998-01-01 00:01:00 '

Mysql> SELECT date_sub (' 1998-01-01 00:00:00 ',

-> INTERVAL ' 1 1:1:1 ' Day_second);

-> ' 1997-12-30 22:58:59 '

Mysql> SELECT date_add (' 1998-01-01 00:00:00 ',

-> INTERVAL '-1 ' day_hour);

-> ' 1997-12-30 14:00:00 '

Mysql> SELECT date_sub (' 1998-01-02 ', INTERVAL Day);

-> ' 1997-12-02 '

Mysql> SELECT date_add (' 1992-12-31 23:59:59.000002 ',

-> INTERVAL ' 1.999999 ' Second_microsecond);

-> ' 1993-01-01 00:00:01.000001 '

If you specify a time interval value that is too short (excluding all the interval parts expected by the type keyword), MySQL assumes that you have omitted the leftmost portion of the time interval value. For example, you specify a type of Day_second, and the value of expr is expected to have the day, hour, minute, and second portions. If you specify a value similar to ' 1:10 ', MySQL assumes that the day and hour portions do not exist, then this value represents minutes and seconds. In other words, ' 1:10 ' day_second is interpreted as equivalent to ' 1:10 ' Minute_second. This is equivalent to the way MySQL interprets a time value as a time-consuming rather than a day.

If you add or subtract some content that contains a time portion to a date value, the result is automatically converted to a date-time value:

Mysql> SELECT date_add (' 1999-01-01 ', INTERVAL 1 day);

-> ' 1999-01-02 '

Mysql> SELECT date_add (' 1999-01-01 ', INTERVAL 1 HOUR);

-> ' 1999-01-01 01:00:00 '

If you use a date with a badly formatted error, the result is NULL. If you add MONTH, Year_month, or year, and the date of the resulting date is greater than the date of the added month, this date is automatically adjusted to the maximum date of the month added:

Mysql> SELECT date_add (' 1998-01-30 ', INTERVAL 1 MONTH);

-> ' 1998-02-28 '

    • Date_format (date,FORMAT)

Arranges the format of date values based on the format string.

The following specifiers are available in the format string:

Specifiers

Description

%a

Abbreviated name for weekday (Sun ... Sat)

%b

The abbreviated name of the month. DEC)

%c

month, digital form (0..12)

%d

The month date with the English suffix (0th, 1st, 2nd, 3rd, ...)

%d

Date of the month, digital form (00..31)

%e

Date of the month, digital form (0..31)

%f

Microsecond (000000..999999)

%H

Hours (00..23)

%h

Hours (01..12)

%I

Hours (01..12)

%i

Minutes, digital form (00..59)

%j

Number of days in a year (001..366)

%k

Hours (0..23)

%l

Hours (1..12)

%m

Month name (January. December)

%m

month, digital form (00..12)

%p

A.M. (AM) or PM (PM)

%r

Time, 12-hour system (hours hh: minutes mm: Seconds of SS plus am or PM)

%s

Seconds (00..59)

%s

Seconds (00..59)

%T

Time, 24-hour system (hours hh: minutes mm: Number of seconds ss)

%u

Week (00..53), where Sunday is the first day of the week

%u

Week (00..53), where Monday is the first day of the week

%V

Week (01..53), of which Sunday was the first day of the week; Use at the same time as%x

%v

Week (01..53), of which Monday was the first day of the week; Use at the same time as%x

%w

Weekday name (Sunday. Saturday

%w

Daily of the week (0= Sunday. 6= Saturday)

%x

The year of the week, of which Sunday is the first day of the week, the number form, 4 digits; and%v use simultaneously.

%x

The year of the week, of which Monday is the first day of the week, the number form, 4 digits; and%v use simultaneously.

%Y

Year, digital form, 4 digits

%y

Year, number form (2 digits)

%%

'% ' literal characters

All other characters are copied to the result without explanation.

Note that the '% ' character is required before the format specifier.

The range of month and date specifiers starts at zero because MySQL allows you to store incomplete dates such as ' 2004-00-00 '.

SELECT date_format (' 1997-10-04 22:23:00 ', '%w%m%Y ');
-> ' Saturday October 1997 '
SELECT date_format (' 1997-10-04 22:23:00 ', '%h:%i:%s ');
-> ' 22:23:00 '
SELECT date_format (' 1997-10-04 22:23:00 ',
'%d%y%a%d%m%b%j ');
-> ' 4th Sat Oct 277 '
SELECT date_format (' 1997-10-04 22:23:00 ',
'%H%k%I%r%T%s%w ');
-> ' 10:23:00 PM 22:23:00 00 6 '
SELECT date_format (' 1999-01-01 ', '%x%V ');
-> ' 1998 52 '
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.