Shulou(Shulou.com)05/31 Report--

This article mainly explains the "use of mysql date function", the content of the explanation is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "the use of mysql date function" bar!

Here is an example of using a date function. The following query selects all records with a date_col value within the last 30 days:

Mysql > SELECT something FROM table

WHERE TO_DAYS (NOW ())-TO_DAYS (date_col) select DAYOFWEEK ('1998-02-03')

-> 3

WEEKDAY (date)

Returns the weekly index of date (0 = Monday, 1 = Tuesday,... 6 = Sunday).

Mysql > select WEEKDAY ('1997-10-04 22 purge 23purl 00')

-> 5

Mysql > select WEEKDAY ('1997-11-05')

-> 2

DAYOFMONTH (date)

Returns the mid-month date of the date, in the range of 1 to 31.

Mysql > select DAYOFMONTH ('1998-02-03')

-> 3

DAYOFYEAR (date)

Returns the number of days in a year for date, in the range of 1 to 366.

Mysql > select DAYOFYEAR ('1998-02-03')

-> 34

MONTH (date)

Returns the month of the date, ranging from 1 to 12.

Mysql > select MONTH ('1998-02-03')

-> 2

DAYNAME (date)

Returns the name of the week of date.

Mysql > select DAYNAME ("1998-02-05")

-> 'Thursday'

MONTHNAME (date)

Returns the month name of the date.

Mysql > select MONTHNAME ("1998-02-05")

-> 'February'

QUARTER (date)

Returns the quarter of the year in date, ranging from 1 to 4.

Mysql > select QUARTER ('98-04-01')

-> 2

WEEK (date)

WEEK (date,first)

For places where Sunday is the first day of the week, there is a single parameter that returns the number of weeks of date, ranging from 0 to 52. 2 parameters in the form WEEK () allow

You specify whether the week begins on Sunday or Monday. If the second parameter is 0, the week begins on Sunday, if the second parameter is 1

Starting on Monday.

Mysql > select WEEK ('1998-02-20')

-> 7

Mysql > select WEEK ('1998-02-20)

-> 7

Mysql > select WEEK ('1998-02-20)

-> 8

YEAR (date)

Returns the year of the date, ranging from 1000 to 9999.

Mysql > select YEAR ('98-02-03')

-> 1998

HOUR (time)

Returns the hour of time, ranging from 0 to 23.

Mysql > select HOUR ('10 05purl 03')

-> 10

MINUTE (time)

Returns the minutes of time, ranging from 0 to 59.

Mysql > select MINUTE ('98-02-03 10 purl 05purl 03')

-> 5

SECOND (time)

The number of seconds to return to time, ranging from 0 to 59.

Mysql > select SECOND ('10 05purl 03')

-> 3

PERIOD_ADD (PPJN)

Add N months to stage P (in YYMM or YYYYMM format). Returns the value in the format YYYYMM. Note that the phase parameter P is not a date value.

Mysql > select PERIOD_ADD (9801 Magi 2)

-> 199803

PERIOD_DIFF (P1and P2)

Returns the number of months between periods P1 and P2, which should be in the format YYMM or YYYYMM. Note that the period parameters P1 and P2 are not date values.

Mysql > select PERIOD_DIFF (9802 ~ 199703)

-> 11

DATE_ADD (date,INTERVAL expr type)

DATE_SUB (date,INTERVAL expr type)

ADDDATE (date,INTERVAL expr type)

SUBDATE (date,INTERVAL expr type)

These functions perform date operations. For MySQL 3.22, they are new. ADDDATE () and SUBDATE () are synonyms for DATE_ADD () and DATE_SUB ().

In MySQL 3.23, you can use + and-instead of DATE_ADD () and DATE_SUB (). (see example) date is a specified start date

DATETIME or date value, expr is an expression that specifies the interval value that is added to or subtracted from the start date, and expr is a string; it can start with

A "-" begins to indicate a negative interval. Type is a keyword that indicates how an expression should be interpreted. The EXTRACT (type FROM date) function starts from the date

Returns the "type" interval in. The following table shows how the type and expr parameters are associated: the type value implies the expected expr format

SECOND second SECONDS

MINUTE minute MINUTES

HOUR time HOURS

DAY days DAYS

MONTH monthly MONTHS

YEAR year YEARS

MINUTE_SECOND minutes and seconds "MINUTES:SECONDS"

HOUR_MINUTE hours and minutes "HOURS:MINUTES"

DAY_HOUR days and hours "DAYS HOURS"

YEAR_MONTH year and month "YEARS-MONTHS"

HOUR_SECOND hours, minutes, "HOURS:MINUTES:SECONDS"

DAY_MINUTE days, hours, minutes "DAYS HOURS:MINUTES"

DAY_SECOND days, hours, minutes, seconds "DAYS HOURS:MINUTES:SECONDS"

MySQL allows any punctuation separator in expr format. Indicates that the recommended delimiter is displayed. If the date parameter is a date value and your calculation is only

Contains the YEAR, MONTH, and DAY parts (that is, there is no time part), and the result is a date value. Otherwise, the result is a data value.

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 10" DAY_HOUR)

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

Mysql > SELECT DATE_SUB (1998-01-02, INTERVAL 31 DAY)

-> 1997-12-02

Mysql > SELECT EXTRACT (YEAR FROM "1999-07-02")

-> 1999

Mysql > SELECT EXTRACT (YEAR_MONTH FROM "1999-07-02 01:02:03")

-> 199907

Mysql > SELECT EXTRACT (DAY_MINUTE FROM "1999-07-02 01:02:03")

-> 20102

If you specify an interval value that is too short (excluding the interval part expected by the type keyword), MySQL assumes that you omit the leftmost interval value. For example,

If you specify that a type is DAY_SECOND, the value expr is expected to have days, hours, minutes, and seconds. If you specify a value like 1:10

MySQL assumes that the day and hour parts are lost and the values represent minutes and seconds. In other words, "1:10" DAY_SECOND is equivalent to "1:10" MINUTE_SECOND.

This is ambiguous to the way that MySQL interprets that the time value represents the elapsed time rather than the time of day. If you use a really incorrect date

The result is NULL. If you increase MONTH, YEAR_MONTH, or YEAR and the resulting date is greater than the maximum number of days in the new month, the date is adjusted with the maximum day in the new moon.

Mysql > select DATE_ADD ('1998-01-30, Interval 1 month)

-> 1998-02-28

Note that the words INTERVAL and type keywords from the previous example are not case-sensitive.

TO_DAYS (date)

Give a date date and return a number of days (from the number of days in the year).

Mysql > select TO_DAYS (950501)

-> 728779

Mysql > select TO_DAYS ('1997-10-07')

-> 729669

TO_DAYS () is not intended to be used to use the values before the Gregorian calendar (1582).

FROM_DAYS (N)

Give a number of days N and return a date value.

Mysql > select FROM_DAYS (729669)

-> '1997-10-07'

TO_DAYS () is not intended to be used to use the values before the Gregorian calendar (1582).

DATE_FORMAT (date,format)

Format the date value according to the format string. The following modifiers can be used in the format string:% M month name (January... December)

% W week name (Sunday... Saturday)

% D the date of the month with the English prefix (1st, 2nd, 3rd, etc.). )

% Y year, number, 4 digits

% y year, number, 2 digits

% an abbreviated name of the week (Sun... Sat)

Number of days in% d month, number (00. 31)

Number of days in% e month, number (0... 31)

% m month, number (01... 12)

% c month, number (1 …... 12)

% b abbreviated month name (Jan... Dec)

% j days of the year (001... 366)

% H hours (00. 23)

% k hours (0. 23)

% h hours (01 …... 12)

% I hours (01 …... 12)

% l hours (1 …... 12)

% I minutes, number (00. 59)

% r time, 12 hours (hh:mm:ss [AP] M)

% T time, 24 hours (hh:mm:ss)

% s seconds (00. 59)

% s seconds (00. 59)

% p AM or PM

% w days in a week (0=Sunday... 6=Saturday)

% U week (0 …... Here Sunday is the first day of the week

% u weeks (0. Here Monday is the first day of the week

%% one word "%".

All other characters are copied to the result without explanation.

Mysql > select DATE_FORMAT ('1997-10-04 22 2300,'% W% M% Y')

-> 'Saturday October 1997'

Mysql > select DATE_FORMAT ('1997-10-04 22 2300,'% HRV% iRO% s')

->'22 2312 00'

Mysql > select DATE_FORMAT ('1997-10-04 22 purl 23purl 00'

'% D% y% a% d% m% b% j')

-> '4th 97 Sat 04 10 Oct 277'

Mysql > select DATE_FORMAT ('1997-10-04 22 purl 23purl 00'

'% H% k% I% r% T% S% w')

->'22 22 10 10:23:00 PM 22:23:00 00 6'

In MySQL3.23,% is required before the format modifier character. In earlier versions of MySQL,% was optional.

TIME_FORMAT (time,format)

This works like the DATE_FORMAT () function above, but the format string can only contain format modifiers that handle hours, minutes, and seconds.

Other modifiers produce a null value or 0.

CURDATE ()

CURRENT_DATE

Returns today's date value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context.

Mysql > select CURDATE ()

-> '1997-12-15'

Mysql > select CURDATE () + 0

-> 19971215

CURTIME ()

CURRENT_TIME

Returns the current time value in 'HH:MM:SS' or HHMMSS format, depending on whether the function is used in a string or in the context of a number.

Mysql > select CURTIME ()

->'23 50 14 26'

Mysql > select CURTIME () + 0

-> 235026

NOW ()

SYSDATE ()

CURRENT_TIMESTAMP

Returns the current date and time in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is in a string or in a numeric

The context is used.

Mysql > select NOW ()

-> '1997-12-15 23 23 50 purl 26'

Mysql > select NOW () + 0

-> 19971215235026

UNIX_TIMESTAMP ()

UNIX_TIMESTAMP (date)

Returns a Unix timestamp (the number of seconds from '1970-01-01 00:00:00'GMT) if UNIX_TIMESTAMP () is called with no arguments.

A date parameter is called, which returns the value of seconds starting from '1970-01-01 00 GMT. Date can be a DATE string or a DATETIME

A string, a TIMESTAMP, or a number of local time in YYMMDD or YYYYMMDD format.

Mysql > select UNIX_TIMESTAMP ()

-> 882226357

Mysql > select UNIX_TIMESTAMP ('1997-10-04 22 purge 23purl 00')

-> 875996580

When UNIX_TIMESTAMP is used for a TIMESTAMP column, the function accepts the value directly, with no implicit "string-to-unix-timestamp" transformation.

FROM_UNIXTIME (unix_timestamp)

Returns the value represented by the unix_timestamp parameter in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is in a string

Or is used in a digital context.

Mysql > select FROM_UNIXTIME (875996580)

-> '1997-10-04 22 22 purl 2300'

Mysql > select FROM_UNIXTIME (875996580) + 0

-> 19971004222300

FROM_UNIXTIME (unix_timestamp,format)

Returns a string representing the Unix time stamp, formatted according to the format string. Format can contain bars listed with the DATE_FORMAT () function

The same modifier.

Mysql > select FROM_UNIXTIME (UNIX_TIMESTAMP ()

'y D M h:%i:%s x')

-> '1997 23rd December 03:43:30 x'

SEC_TO_TIME (seconds)

Returns the seconds parameter, transformed into hours, minutes, and seconds, with values formatted as' HH:MM:SS' or HHMMSS, depending on whether the function is in a string or in a number

Is used in the context.

Mysql > select SEC_TO_TIME (2378)

-> '00Rose 39RU 38'

Mysql > select SEC_TO_TIME (2378) + 0

-> 3938

TIME_TO_SEC (time)

Returns the time parameter, which is converted to seconds.

Mysql > select TIME_TO_SEC ('22 2300')

-> 80580

Mysql > select TIME_TO_SEC ('00GRV 39RV 38')

-> 2378

-

Mysql date processing function mysql date processing function

Mysql has its own functions for formatting dates, which can be used in query statements.

DATE_FORMAT (date,format)

Format the date value according to the format string. The following modifiers can be used in the format string:% M month name (January... December)

% W week name (Sunday... Saturday)

% D the date of the month with the English prefix (1st, 2nd, 3rd, etc.). )

% Y year, number, 4 digits

% y year, number, 2 digits

% an abbreviated name of the week (Sun... Sat)

Number of days in% d month, number (00. 31)

Number of days in% e month, number (0... 31)

% m month, number (01... 12)

% c month, number (1 …... 12)

% b abbreviated month name (Jan... Dec)

% j days of the year (001... 366)

% H hours (00. 23)

% k hours (0. 23)

% h hours (01 …... 12)

% I hours (01 …... 12)

% l hours (1 …... 12)

% I minutes, number (00. 59)

% r time, 12 hours (hh:mm:ss [AP] M)

% T time, 24 hours (hh:mm:ss)

% s seconds (00. 59)

% s seconds (00. 59)

% p AM or PM

% w days in a week (0=Sunday... 6=Saturday)

% U week (0 …... Here Sunday is the first day of the week

% u weeks (0. Here Monday is the first day of the week

%% one word "%".

If you need to use php processing, you can first use the strtotime function to convert the date into a unix timestamp, and then use the date function to format [@ more@]

Thank you for your reading, the above is the content of "the use of mysql date function", after the study of this article, I believe you have a deeper understanding of the use of mysql date function, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.