Date and Time Functions  12.7
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()
LOCALTIMESTAMPLOCALTIMESTAMP() 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()的行为不同,后者的行为返回一个恒定时间,该时间指示语句开始执行的时间。 

Date and Time Functions_sed