Summary of date functions in MySQL

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)

##Example

• 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_sub( date, INTERVAL expr type )

• Date minus the specified time interval.

If expr is positive, then this time interval is subtracted; if expr is negative, subtracting a negative number is equal to addition

• 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!