Date and time functions refer to functions used to analyze and process date values and time values in formulas. In this article, we will share with you a summary of MYSQL date functions.
See 7.3.6 Date and Time Types for a description of the range of values each type has and the valid formats for specifying date and time values.
Here is an example of using date functions. The following query selects all records whose date_col value is within the last 30 days:
mysql> SELECT something FROM table
WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30 ;
DAYOFWEEK(date)
Returns the week index of date (1=Sunday, 2=Monday, ...7=Saturday). These index values correspond to the ODBC standard.
mysql> select DAYOFWEEK('1998-02-03');
-> 3
WEEKDAY(date)
Returns the week index of date (0=Monday, 1= Tuesday, …6= Sunday).
mysql> select WEEKDAY('1997-10-04 22:23:00');
-> 5
mysql> select WEEKDAY('1997-11-05');
- > 2
DAYOFMONTH(date)
Returns the day of the month of date, in the range of 1 to 31.
mysql> select DAYOFMONTH('1998-02-03');
-> 3
DAYOFYEAR(date)
Returns the number of days of the year in date, between 1 and 366 within the range.
mysql> select DAYOFYEAR('1998-02-03');
-> 34
MONTH(date)
Returns the month of date, ranging from 1 to 12.
mysql> select MONTH('1998-02-03');
-> 2
DAYNAME(date)
Returns the day of the week name of date.
mysql> select DAYNAME("1998-02-05");
-> 'Thursday'
MONTHNAME(date)
Returns the month name of 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 Where Sunday is the first day of the week, there is a single argument that returns the week number of date, in the range 0 to 52. The 2 argument form WEEK() allows
you to specify whether the week starts on Sunday or Monday. If the second parameter is 0, the week starts on Sunday, if the second parameter is 1,
starts on Monday.
mysql> select WEEK('1998-02-20');
-> 7
mysql> select WEEK('1998-02-20',0);
-> 7
mysql> select WEEK('1998-02-20',1);
-> 8
YEAR(date)
Returns the year of 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:05:03');
-> 10
MINUTE(time)
Returns the minute of time, ranging from 0 to 59.
mysql> select MINUTE('98-02-03 10:05:03');
-> 5
SECOND(time)
Returns the number of seconds of time, the range is 0 to 59.
mysql> select SECOND('10:05:03');
-> 3
PERIOD_ADD(P,N)
Add N months to stage P (in format YYMM or YYYYMM). Returns the value in the format YYYYMM. Note that the phase parameter P is not a date value.
mysql> select PERIOD_ADD(9801,2);
-> 199803
PERIOD_DIFF(P1,P2)
Returns the number of months between periods P1 and P2, P1 and P2 should 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. As of MySQL 3.22, they are new. ADDDATE() and SUBDATE() are synonyms of 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 the start date, expr is an expression that specifies the interval value to be added to or subtracted from the start date, expr is a string; it can start with
A "-" starts to indicate a negative interval. type is a keyword that specifies how the expression should be interpreted. The EXTRACT(type FROM date) function returns the "type" interval from date
. The following table shows how the type and expr parameters are related: type value meaning expected expr format
SECOND seconds SECONDS
MINUTE minutes MINUTES
HOUR time HOURS
DAY days DAYS
MONTH months MONTHS
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 Years and months "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 delimiter in expr format. Indicates that recommended delimiters are displayed. If the date argument is a DATE value and your calculation is only
Contains YEAR, MONTH, and DAY components (that is, no time component), and 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: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 expected by the type keyword), MySQL assumes that you omitted the interval value The leftmost part. For example,
If you specify a type of DAY_SECOND, the value expr is expected to have day, hour, minute, and second components. If you specify a value like "1:10",
MySQL assumes that the day and hour parts are missing and the value represents minutes and seconds. In other words, "1:10" DAY_SECOND is interpreted in a way that it is equivalent to "1:10" MINUTE_SECOND
, which is ambiguous with the way MySQL interprets the TIME value to represent an elapsed time rather than as a time of day. sex. If you use a really incorrect date,
the result is NULL. If you increment MONTH, YEAR_MONTH, or YEAR and the resulting date is greater than the maximum number of days in the new month, the days are adjusted to the maximum number of days in the new month.
mysql> select DATE_ADD('1998-01-30', Interval 1 month);
-> 1998-02-28
Note that the word INTERVAL from the previous example and type keywords are not case-sensitive.
TO_DAYS(date)
Given a date date, return a number of days (the number of days from year 0).
mysql> select TO_DAYS(950501);
-> 728779
mysql> select TO_DAYS('1997-10-07');
-> 729669
TO_DAYS( ) is not intended to be used using values before the advent of the Gregorian calendar (1582).
FROM_DAYS(N)
Given a number of days N, return a DATE value.
mysql> select FROM_DAYS(729669);
-> '1997-10-07'
TO_DAYS() is not intended to be used with values before the advent of 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 day of the month with English prefix (1st, 2nd, 3rd, etc.)
%Y Year, number, 4 digits
%y Year, number, 2 digits
%a Abbreviated day of the week name (Sun......Sat)
%d Number of days in the month, number (00……31)
%e Number of days in the month, number (0……31)
%m Month, number (01……12)
%c Month, Number (1...12)
%b Abbreviated month name (Jan...Dec)
%j Number of days in a year (001...366)
%H Hour (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 The number of days in a week (0=Sunday…6=Saturday)
%U Week (0……52), where Sunday is the first day of the week
%u Week (0……52), where Monday is the first day of the week
%% A text "%".
All other characters are copied to the result without interpretation.
mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
-> 'Saturday October 1997'
mysql> select DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
-> '22:23:00'
mysql> select DATE_FORMAT(' 1997-10-04 22:23:00',
'%D %y %a %d %m %b %j');
-> '4th 97 Sat 04 10 Oct 277'
mysql> select DATE_FORMAT('1997-10-04 22:23: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 is used like the DATE_FORMAT() function above, but the format string can only contain those 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 numeric context.
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 in a character Strings are still used in the
context of numbers.
mysql> select NOW();
-> '1997-12-15 23:50:26'
mysql> select NOW() + 0;
-> 19971215235026
UNIX_TIMESTAMP()
UNIX_TIMESTAMP(date)
If called without parameters, returns a Unix timestamp (the number of seconds since '1970-01-01 00:00:00' GMT). If UNIX_TIMESTAMP() is called with a
date argument, it returns the number of seconds since '1970-01-01 00:00:00' GMT. date can be a DATE string, a DATETIME
string, a TIMESTAMP, or a number in local time in YYMMDD or YYYYMMDD format.
mysql> select UNIX_TIMESTAMP();
-> 882226357
mysql> select UNIX_TIMESTAMP('1997-10-04 22:23:00');
-> 875996580
When UNIX_TIMESTAMP is used for a TIMESTAMP column, the function will accept the value directly, without the 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 used in a numerical context.
mysql> select FROM_UNIXTIME(875996580);
-> '1997-10-04 22:23:00'
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 the same modifiers as those listed for 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, converted into hours, minutes and seconds. The value is formatted in 'HH:MM:SS' or HHMMSS, depending on whether the function is in a string or Used in the context of numbers
.
mysql> select SEC_TO_TIME(2378);
-> '00:39:38'
mysql> select SEC_TO_TIME(2378) + 0;
-> 3938
TIME_TO_SEC(time)
Return the time parameter and convert it into seconds.
mysql> select TIME_TO_SEC('22:23:00');
-> 80580
mysql> select TIME_TO_SEC('00:39:38');
-> 2378
The above content is a summary of MYSQL date functions, I hope it can help everyone.
Related recommendations:
Date functions and flexible use of function encapsulation
##Commonly used mysql date functions
MYSQL date function collection
The above is the detailed content of MYSQL date function summary. For more information, please follow other related articles on the PHP Chinese website!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
