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

The purpose of this article is to share with you about the common date comparison and calculation functions in mysql. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Implementation of time comparison in MySql

Unix_timestamp ()

The unix_timestamp function can take a parameter or no argument.

Its return value is an unsigned integer. Takes no argument, which returns the number of seconds that have elapsed since 00:00:00 on January 1, 1970

If you use a parameter whose type is a time type or a string representation of the time type, it is the number of seconds passed from 1970-01-01 00:00:00 to the specified time.

With this function, you can naturally convert a time comparison into an unsigned integer comparison.

For example, to determine whether a time is within a range

Unix_timestamp (time) between unix_timestamp ('start') and unix_timestamp ('end')

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 5mysql > 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. The two-parameter form WEEK () allows you to specify whether the week begins on Sunday or Monday. If the second parameter is 0, the week starts on Sunday, and if the second parameter is 1, it starts on Monday.

Mysql > select WEEK ('1998-02-20');-> 7mysql > select WEEK ('1998-02-20);-> 7mysql > 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 ('10VOV 05RU 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 ('10VR 05R 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 DATETIME or date value that specifies a start date, expr is an expression that specifies an interval value that is added to or subtracted from the start date, and expr is a string; it can start with a "-" to indicate a negative interval. Type is a keyword that indicates how an expression should be interpreted. The EXTRACT (type FROM date) function returns the "type" interval from the date. 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 contains only the YEAR, MONTH, and DAY parts (that is, no time part), the result is a date value. Otherwise the result is a DATETIME value.

Mysql > SELECT "1997-12-31 23:59:59" + INTERVAL 1 SECOND;- > 1998-01-01 00:00:00mysql > SELECT INTERVAL 1 DAY + "1997-12-31";-> 1998-01-01mysql > SELECT "1998-01-01"-INTERVAL 1 SECOND;- > 1997-12-31 23:59:59mysql > SELECT DATE_ADD ("1997-12-31 23:59:59", INTERVAL 1 SECOND) -> 1998-01-01 00:00:00mysql > SELECT DATE_ADD ("1997-12-31 23:59:59", INTERVAL 1 DAY);-> 1998-01-01 23:59:59mysql > SELECT DATE_ADD ("1997-12-31 23:59:59" INTERVAL "1:1" MINUTE_SECOND);-> 1998-01-01 00:01:00mysql > SELECT DATE_SUB ("1998-01-01 00:00:00", INTERVAL "1 1:1:1" DAY_SECOND) -> 1997-12-30 22:58:59mysql > SELECT DATE_ADD ("1998-01-01 00:00:00", INTERVAL "- 1 10" DAY_HOUR);-> 1997-12-30 14:00:00mysql > SELECT DATE_SUB ("1998-01-02", INTERVAL 31 DAY);-> 1997-12-02mysql > SELECT EXTRACT (YEAR FROM "1999-07-02");-> 1999mysql > SELECT EXTRACT (YEAR_MONTH FROM "1999-07-02 01:02:03") -> 199907mysql > 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 equals "1:10"

MINUTE_SECOND 's interpretation is ambiguous to the way that MySQL interprets that the time value represents the elapsed time rather than the time of the 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);-> 728779mysql > select TO_DAYS ('1997-10-07');-> 729669

FROM_DAYS (N)

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

Mysql > select FROM_DAYS (729669);-> '1997-10-07'

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 date of the month with 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 Saturday October 1997'mysql 2300 Saturday October 1997'mysql% M% Y');->' Saturday October 1997'mysql > select DATE_FORMAT ('1997-10-04 22 Saturday October 1997'mysql 2300% HV% iRV% s');-> '22:23:00'mysql > select DATE_FORMAT (' 1997-10-04 2223 purse 00charge% a% a% d% m% b% j') -> '4th 97 Sat 04 10 Oct 277'mysql > select DATE_FORMAT (' 1997-10-04 22 22 PM 2300 Oct 277'mysql% 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 position-> 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: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 used in a string or in the context of a number.

Mysql > select NOW ();-> '1997-12-1523: 50:26'mysql > select NOW () + 0 Tinci-> 19971215235026

UNIX_TIMESTAMP ()

UNIX_TIMESTAMP (date)

If there is no argument call, returns a Unix timestamp (seconds from '1970-01-01 00:00:00'GMT). If UNIX_TIMESTAMP () is called with a date parameter, it returns the second value from' 1970-01-01 00:00:00'GMT. Date can be a DATE string, a DATETIME string, a TIMESTAMP, or a number of local time in YYMMDD or YYYYMMDD format.

Mysql > select UNIX_TIMESTAMP ();-> 882226357mysql > select UNIX_TIMESTAMP ('1997-10-04 22 882226357mysql 2315);-> 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-0422: 23:00'mysql > select FROM_UNIXTIME (875996580) + 0 Ten-> 19971004222300

FROM_UNIXTIME (unix_timestamp,format)

Returns a string representing the Unix time stamp, formatted according to the format string. Format can contain the same modifiers as the entries listed in the DATE_FORMAT () function.

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 used in a string or in a numeric context.

Mysql > select SEC_TO_TIME (2378);-> '00:39:38'mysql > select SEC_TO_TIME (2378) + 0 position-> 3938

TIME_TO_SEC (time)

Returns the time parameter, which is converted to seconds.

Mysql > select TIME_TO_SEC ('22 80580mysql 2300');-> 80580mysql > select TIME_TO_SEC ('00 80580mysql 39 °38');-> 2378

Mysql has many date functions, and sometimes the function used for date comparison is not necessarily a function. It may be a combination of multiple functions, so you should use your imagination.

Thank you for reading! This is the end of this article on "what are the commonly used date comparison and calculation functions in mysql?". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

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.