MySQL的日期函数较多,只需要掌握常用的即可,常用的日期或时间函数参考如下的表格:
表 STYLEREF 1 \s 4- SEQ 表 * ARABIC \s 1 1 MySQL的日期函数
函数 | 函数功能描述 | 函数举例 |
---|---|---|
DAYOFWEEK(DATE) | 返回DATE的星期索引(1= Sunday,2= Monday,... 7=Saturday) | mysql> SELECT DAYOFWEEK('2016-05-24');+-------------------------+ |
DAYOFYEAR(DATE) | 返回DATE是一年中的第几天,范围为1到366 | mysql> SELECT DAYOFYEAR('2016-05-24');+-------------------------+ |
HOUR(TIME)/MINUTE(TIME)/SECOND(TIME) | 返回TIME的小时值/分钟值/秒值,范围为0到23 | mysql> SELECT HOUR('10:05:03'),MINUTE('10:05:03'),SECOND('10:05:03');+------------------+--------------------+--------------------+ |
DATE_FORMAT(DATE,FORMAT) | 依照FORMAT字符串格式化DATE值,修饰符的含义:%M:月的名字(January..December)%W:星期的名字(Sunday..Saturday)%D:有英文后缀的某月的第几天(0th,1st,2nd,3rd等) %Y:4位数字年份%y:2位数字年份 %m:月,数字(00..12)%c:月,数字(0..12) %d:代表月份中的天数,格式为(00……31)%e:代表月份中的天数,格式为(0……31) %x:周值的年份,星期一是一个星期的第一天,数字的,4位,与“%v”一同使用%a:缩写的星期名(Sun..Sat) %H:小时(00..23)%k:小时(0..23)%h:小时(01..12)%I:小时(01..12)%l:小时(1..12) %i:代表分钟, 格式为(00……59) 。只有这一个代表分钟,大写的I不代表分钟代表小时 %r:代表 时间,格式为12 小时(hh:mm:ss [AP]M))%T:代表 时间,格式为24 小时(hh:mm:ss) %S:秒(00..59)%s:秒(00..59) %p:AM或PM%w:一周中的天数(0=Sunday..6=Saturday) | mysql> SELECT DATE_FORMAT('2016-05-24', '%W %M %Y');+---------------------------------------+ |
STR_TO_DATE() | 将字符串转换为日期类型 | mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');+---------------------------------------+ |
CURDATE()/CURRENT_DATE | 以“YYYY-MM-DD”或“YYYYMMDD”格式返回当前的日期值 | mysql> SELECT CURDATE(),CURRENT_DATE;+------------+--------------+ |
CURTIME()/CURRENT_TIME | 以“HH:MM:SS”或“HHMMSS”格式返回当前的时间值 | mysql> SELECT CURTIME(),CURRENT_TIME();+-----------+----------------+ |
NOW()/SYSDATE()/CURRENT_TIMESTAMP | 以“YYYY-MM-DD HH:MM:SS”或“YYYYMMDDHHMMSS”格式返回当前的日期时间值 | mysql> SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP;+---------------------+---------------------+---------------------+ |
SEC_TO_TIME(NUMBER) | 以“HH:MM:SS”或“HHMMSS”格式返回入参值被转换到时分秒后的值 | mysql> SELECT SEC_TO_TIME(2378);+-------------------+ |
TIME_TO_SEC(TIME) | 将参数TIME转换为秒数后返回 | mysql> SELECT TIME_TO_SEC('22:23:00');+-------------------------+ |
其它的函数请查阅官方文档。
真题1、MySQL中的字符串和日期相互转化的函数是什么?
答案:MySQL中日期转换为字符串使用DATE_FORMAT函数,相当于Oracle中的TO_CHAR函数,而将字符串转换为日期格式,使用的函数为STR_TO_DATE,相当于Oracle中的TO_DATE函数。
STR_TO_DATE函数的使用示例如下所示:
select str_to_date('09/01/2009','%m/%d/%Y');
select str_to_date('20140422154706','%Y%m%d%H%i%s');
select str_to_date('2014-04-22 15:47:06','%Y-%m-%d %H:%i:%s');
12.7 Date and Time Functions
This section describes the functions that can be used to manipulate temporal values. See Section 11.3, “Date and Time Types”, for a description of the range of values each date and time type has and the valid formats in which values may be specified.
Table 12.13 Date and Time Functions
Name | Description |
---|---|
ADDDATE() | Add time values (intervals) to a date value |
- | - |
ADDTIME() | Add time |
CONVERT_TZ() | Convert from one time zone to another |
CURDATE() | Return the current date |
CURRENT_DATE(), CURRENT_DATE | Synonyms for CURDATE() |
CURRENT_TIME(), CURRENT_TIME | Synonyms for CURTIME() |
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP | Synonyms for NOW() |
CURTIME() | Return the current time |
DATE() | Extract the date part of a date or datetime expression |
DATE_ADD() | Add time values (intervals) to a date value |
DATE_FORMAT() | Format date as specified |
DATE_SUB() | Subtract a time value (interval) from a date |
DATEDIFF() | Subtract two dates |
DAY() | Synonym for DAYOFMONTH() |
DAYNAME() | Return the name of the weekday |
DAYOFMONTH() | Return the day of the month (0-31) |
DAYOFWEEK() | Return the weekday index of the argument |
DAYOFYEAR() | Return the day of the year (1-366) |
EXTRACT() | Extract part of a date |
FROM_DAYS() | Convert a day number to a date |
FROM_UNIXTIME() | Format Unix timestamp as a date |
GET_FORMAT() | Return a date format string |
HOUR() | Extract the hour |
LAST_DAY | Return the last day of the month for the argument |
LOCALTIME(), LOCALTIME | Synonym for NOW() |
LOCALTIMESTAMP, LOCALTIMESTAMP() | Synonym for NOW() |
MAKEDATE() | Create a date from the year and day of year |
MAKETIME() | Create time from hour, minute, second |
MICROSECOND() | Return the microseconds from argument |
MINUTE() | Return the minute from the argument |
MONTH() | Return the month from the date passed |
MONTHNAME() | Return the name of the month |
NOW() | Return the current date and time |
PERIOD_ADD() | Add a period to a year-month |
PERIOD_DIFF() | Return the number of months between periods |
QUARTER() | Return the quarter from a date argument |
SEC_TO_TIME() | Converts seconds to 'HH:MM:SS' format |
SECOND() | Return the second (0-59) |
STR_TO_DATE() | Convert a string to a date |
SUBDATE() | Synonym for DATE_SUB() when invoked with three arguments |
SUBTIME() | Subtract times |
SYSDATE() | Return the time at which the function executes |
TIME() | Extract the time portion of the expression passed |
TIME_FORMAT() | Format as time |
TIME_TO_SEC() | Return the argument converted to seconds |
TIMEDIFF() | Subtract time |
TIMESTAMP() | With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments |
TIMESTAMPADD() | Add an interval to a datetime expression |
TIMESTAMPDIFF() | Subtract an interval from a datetime expression |
TO_DAYS() | Return the date argument converted to days |
TO_SECONDS() | Return the date or datetime argument converted to seconds since Year 0 |
UNIX_TIMESTAMP() | Return a Unix timestamp |
UTC_DATE() | Return the current UTC date |
UTC_TIME() | Return the current UTC time |
UTC_TIMESTAMP() | Return the current UTC date and time |
WEEK() | Return the week number |
WEEKDAY() | Return the weekday index |
WEEKOFYEAR() | Return the calendar week of the date (1-53) |
YEAR() | Return the year |
YEARWEEK() | Return the year and week |
Name | Description |
---|