Summary of date functions in MySQL

黄舟

Release： 2017-09-26 14:09:39

Original

3252 people have browsed it

Regarding the MySQL date and time functions, I can always find some new features every time, so I simply make a unified arrangement and supplement the note library. There may be some omissions, welcome to add.

This article has compiled 30 time and date functions, divided into 4 paragraphs for analysis, see below for details.

1 Get time function

Get the current time

sysdate()
now(), current_timestamp(), current_timestamp, LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, LOCALTIMESTAMP()

rest ns

sleep(n)

Example

Sameness: Get the current time
Difference: When is the returned time, the time value of executing SQL or calling the function
sysdate(), returns the time value at the time when the function is called
now (), current_timestamp()/current_timestamp, LOCALTIME, LOCALTIME(), LOCALTIMESTAMP, LOCALTIMESTAMP() returns the time value when the statement is called;
Please clear it clearly during use The difference between these three
now(), current_timestamp(), current_timestamp, LOCALTIME, LOCALTIME() , LOCALTIMESTAMP , LOCALTIMESTAMP()

2 Time calculation function

Time difference

##datediff(date1, date2), timediff(time1, time2), timestampdiff(interval,datetime_expr1,datetime_expr2)

Time addition and subtraction

##date_sub(date, INTERVAL expr type)
DATE_ADD(date,INTERVAL expr type)
##adddate (date,INTERVAL expr type), timestampadd(interval, count, timestamp)

The difference between the two times

interval can be:
FRAC_SECOND milliseconds, SECOND seconds, MINUTE minutes , HOUR hours
DAY days, WEEK weeks, MONTH months, QUARTER quarters, YEAR years
The difference time of subtracting two times, note that it needs to be in the same format
two Subtract the dates to get the number of days. Note that the two must be in the same format
##datediff(date1, date2)
timediff( time1 , time2 )
##TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2)

Date minus the specified time interval.

date parameter is legal date expression. The expr parameter is the time interval you wish to add. Type can refer to the following
@date = '2017-09-09 09:09:09'

##MICROSECONDOne-tenth of a second, ±ndate_sub( @date ,INTERVAL 2 MICROSECOND )2017-09-09 09:09SECONDsecondsdate_sub( @date ,INTERVAL 2 SECOND )2017-09-09 09:09:##MINUTE07HOUR07##DAY天date_sub( @date ,INTERVAL 2 DAY )2017-09-09:09:09WEEKweekdate_sub( @date ,INTERVAL 2 WEEK )2017- 09:09:09MONTHmonthdate_sub( @date ,INTERVAL 2 MONTH )2017--09 09: 09:09QUARTER(Quarter, 3 months)Quarterdate_sub( @date ,INTERVAL 2 QUARTER )2017--09 09:09:09YEAR年date_sub( @date ,INTERVAL 2 YEAR )-09-09 09:09:09SECOND_MICROSECONDExpression, from seconds to ten 1 seconddate_sub( @date ,INTERVAL '2.2' SECOND_MICROSECOND )2017-09-09 09:09:MINUTE_MICROSECONDExpression, from minutes to tenths of a seconddate_sub( @date ,INTERVAL '2:2.2' MINUTE_MICROSECOND )2017-09 -09 09:MINUTE_SECONDExpression, from minutes to secondsdate_sub( @date , INTERVAL '2:2' MINUTE_SECOND )2017-09-09 09:HOUR_MICROSECONDExpression Formula, from hour to tenth of a seconddate_sub( @date ,INTERVAL '2:2:2.2' HOUR_MICROSECOND )2017-09-09 HOUR_SECONDExpression, hour to seconddate_sub( @date ,INTERVAL '2:2:2' HOUR_SECOND )2017-09-09 HOUR_MINUTEExpression, hours to minutesdate_sub( @date ,INTERVAL '2:2' HOUR_MINUTE )2017-09-09 :09Expression, day to tenth of a second, 07 07:07:06.800000Expression, day to second, 07 07:07:07 Expression, day to minute, 07 07:07Expression, day to hour, 07 07Expression, year to month, 2015-07

Type Value
			:08.999998
			07
minutes	date_sub( @date ,INTERVAL 2 MINUTE )	2017-09-09 09:	:09
hour	date_sub( @date ,INTERVAL 2 HOUR)	2017-09-09	:07:09
		07
		08-26
		07
		03
		2015
		06.980000
		07:06.800000
		07:07
		07:07:06.800000
		07:07:07
		07:07	##DAY_MICROSECOND
date_sub( @date ,INTERVAL '2 2:2:2.2' DAY_MICROSECOND )	2017-09-		DAY_SECOND
date_sub( @date ,INTERVAL ' 2 2:2:2' DAY_SECOND )	2017-09-		DAY_MINUTE
date_sub( @date ,INTERVAL '2 2:2 ' DAY_MINUTE )	2017-09-	:09	DAY_HOUR
date_sub( @date ,INTERVAL '2 2' DAY_HOUR )	2017 -09-	:09:09	YEAR_MONTH
date_sub( @ date ,INTERVAL '2 2' YEAR_MONTH )		-09 09:09:09	##DATE_ADD(date,INTERVAL expr type) The date is added to the specified time interval. The usage method is the same as date_sub ##adddate(date,INTERVAL expr type) The date is increased by n days The date is increased by the specified time interval, using the same method as DATE_ADD adddate(date,INTERVAL expr type ) adddate(date, n ) timestampadd(interval, count, timestamp) ##Year quarter Month Day Week Hour minute second millisecond Time increasing function 3 Time conversion function ##from_unixtime(), unix_timestamp() The former will Timestamp is converted into date time format, which converts date time format into timestamp makedate(year, day_of_year ), maketime(hour,minute,second) ##makedate Convert to date according to the year and the day of the year maketime Convert to time based on hours, minutes and seconds ##date(datetime), timestamp( date [,time]) The former converts date and time format into date format (only containing year, month and day), and the latter converts date into Date time format ##convert_tz(datetime, from_tz, to_tz) According to the time in A time zone, return the time in B time zone ## date_format(datetime,format), str_to_date(string,format) The former converts the date and time into text in the specified format, and the latter converts the text For formatting date and time Example Practical application SELECT NOW(),DATE_FORMAT(NOW(),'%Y-%m-%d') '%Y-%m-%d'; Year Month ##日 ##Time module hours minutes Seconds Week ##%X years, where Sunday is the first day of the week, 4 digits, used with %V %x year, Monday is the first day of the week, 4 digits, used with %v %Y years, 4 digits %y years, 2 digits ##%b Abbreviated month name ##%c Month, numerical value ##%M Month name %m Month, value (00-12) ##%D %D with English prefix in the month Day %d Day of the month, value (00-31) %e Day of the month, value (0-31) %j Day of the year (001-366) ##%T Time, 24-hour (hh:mm:ss) %r Time, 12-hour (hh:mm:ss AM or PM) %H Hours(00-23) %k Hours(0-23) ##%h Hours( 01-12) %I hours (01-12) #%h and %I, there is no difference (capital i) %l hour (1-12) lower case L %p AM or PM #%i Minutes, value (00-59) %S Seconds(00-59) %s Seconds(00-59) ##%f Micro Seconds ##%a Abbreviation of week name ##%W Week name, full English name Saturday %w Day of the week (0=Sunday, 6=Saturday, 0-6) ##%U Week(00-52) Sunday is the first day of the week %u Week(00-52) Monday is the first day of the week %V Week(01-53) Sunday is the first day of the week , use with %x Week (01-53) Monday is the first day of the week, use with %x Convert string to date format, which characters in string are year, month, day and time, matched by format Time zone conversion datetime is the time zone of from_zt, and returns the time in to_tz time zone Time stamp to conversion Date time format conversion For the date format makedate is converted into a date based on the year and the day of the year maketime Convert time to time based on hours, minutes and seconds ##Convert time to timestamp Convert timestamp to time from_unixtime unix_timestamp makedate (year, day_of_year),maketime(hour,minute,second) ##date(datetime) timestamp(date), timestamp(date,time) ##convert_tz(datetime,from_tz,to_tz) str_to_date(string,format) ##date_format 4 Get some functions Get the days to_days (datetime), dayofyear(datetime), dayofmonth(datetime) ##todays Return from '0000-00 -00' How many days are there between datetime and datetime dayofyear , return datetime and the day of the year this day is dayofmonth , returns datetime which day of the month this day is Second conversion ##time_to_sec(time)，sec_to_time(number) The former is converted into seconds based on time, and the latter is converted into time based on seconds

The above is the detailed content of Summary of date functions in MySQL. For more information, please follow other related articles on the PHP Chinese website!