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 |
NOW()
现在的日期+时间
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP
CURDATE()
select CURDATE() 当天
CURRENT_DATE(), CURRENT_DATE
SELECT CURDATE() + 0 返回 20201118
CURTIME()
当前时间
CURRENT_TIME(), CURRENT_TIME
UTC_DATE()
当前的utc日期
UTC_TIME()
当前的utc时间
UTC_TIMESTAMP()
当前的utc日期+utc时间
DAYOFYEAR
1年中的第几天
Return the day of the year (1-366)
select DAYOFYEAR(CURDATE())
DAYOFMONTH()
几号
SELECT DAYOFMONTH('2020-11-18')
Return the day of the month (0-31)
DAYOFWEEK
本周第几天,星期天是1,星期六是7 注意这个和WEEKDAY区别
WEEKDAY(date)
(0
= Monday, 1
= Tuesday, … 6
= Sunday) 注意这个和DAYOFWEEK区别
select DAYOFWEEK('2020-11-22')
MONTH()
几月
11月是11
SELECT MONTH('2020-11-18')
DATE
SELECT DATE('2003-12-31 01:02:03') 返回 2003-12-31
提取日期
HOUR
SELECT HOUR(now())
现在是几点
DATE_SUB
Subtract a time value (interval) from a date 从日期中减去时间值(间隔)
同义词 SUBDATE()
# 10天前
select DATE_SUB(CURDATE(),INTERVAL 10 DAY)
# 10年前
select DATE_SUB(CURDATE(),INTERVAL 10 YEAR)
DATE_ADD()
Add time values (intervals) to a date value
同义词 ADDDATE()
将时间值(间隔)添加到日期
# 1天后
SELECT DATE_ADD(now(),INTERVAL 1 DAY)
# 1月后
SELECT DATE_ADD(now(),INTERVAL 1 MONTH)
# 1年后
SELECT DATE_ADD(now(),INTERVAL 1 YEAR)
DATEDIFF
returns expr1 − expr2 仅计算日期
SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30')
LAST_DAY
SELECT LAST_DAY(now())
本月最后1天
STR_TO_DATE
SELECT STR_TO_DATE('11/18/2020', '%m/%d/%Y')
SLEEP
SYSDATE()返回执行时间。 这与NOW()的行为不同,后者的行为返回一个恒定时间,该时间指示语句开始执行的时间。