What are the commonly used date and time functions in MySQL? This article will introduce it to you, as shown in the figure below:
CURDATE(), CURRENT_DATE() returns the current date 'YYYY-MM-DD'
CURTIME(), CURRENT_TIME() returns the current time 'HH:MM:SS'
CURRENT_TIMESTAMP()
LOCALTIME()
NOW()
SYSDATE() returns the current date and time 'YYYY-MM-DD HH:MM:SS'
UNIX_TIMESTAMP( date) returns the number of seconds after Unix timestamp 1970-01-01 00:00:00. date can be a specified time, such as DATE string, DATETIME string, TIMESTAMP string, etc.
FROM_UNIXTIME(date) Converts unix timestamp to common format, which is the inverse function of UNIX_TIMESTAMP(date)
UTC_DATE() Returns the current UTC (Universal Standard Time) date value
UTC_TIME() returns the current UTC time value
--8 hours difference
MONTH(date ) Returns the month of date 1-12
MONTHNAME(date) Returns the full English name of the month of date
DAYNAME(date) Returns the day of the week corresponding to date The English name of a certain day
##DAYOFWEEK(date) returns the index position of date in the week, 1--Sunday...7-SaturdayWEEKDAY(date) returns the index position of date in the week 0-Monday 6-Sunday --The above 2017-07-03 is MondayWEEK(date) returns the week number of the year in date. It can have a second parameter to determine whether it starts on Monday or Sunday and the return value is 0~53 or 1~53 WEEKOFYEAR(date) Returns the week number of date in the year DAYOFYEAR(date) Returns the date of date in the year The number of days in range 1~366DAYOFMONTH(date) Returns the day of the month when date is returned 1~31 YEAR(date ) Return the year of the date --- 00-69 Returns 2000~2069 70-99 Returns 1970~1999 QUARTER(date) Returns the quarter Returns 1~4 MINUTE(time) Returns the number of minutes of timeSECOND(time) Returns the number of seconds of timeEXTRACT(type FROM date) Returns the type value extracted from date
##TIME_TO_SEC(time) Returns the number of seconds to convert time
SEC_TO_TIME(sec) Returns the result of converting seconds to hours, minutes and seconds
DATE_ADD(date,INTERVAL expr type)
ADDDATE(date,INTERVAL expr type) Add operation to date
##DATE_SUB(date,INTERVAL expr type)
SUBDATE(date,INTERVAL expr type) Subtract the date ##ADDTIME(date,expr) date-is a date or time expression, expr-is a time expression, return date plus expr time The result SUBTIME(date,expr) returns the time of date minus expr##DATEDIFF(date1, date2 ) Returns the day value of date1-date2
DATE_FORMAT(date,format) Returns the date value in the format specified by format
##TIME_FORMAT(time,format) Returns the format format The time value GET_FORMAT(val_type,format_type) val_type--DATE, DATETIEM, TIME format_type--EUR, INTERVAL , ISO, JIS, USAReturn the corresponding format formatThe above is the detailed content of What are the commonly used date and time functions in MySQL?. For more information, please follow other related articles on the PHP Chinese website!