MySQL _ date function summary and mysql function Summary

MySQL _ date function summary and mysql function Summary

If it is reprinted, please indicate the source of the blog: www.cnblogs.com/xinysu/. The copyright belongs to xiaoradish in the blog garden. Thank you for your support! For MySQL date and time functions, some new features can always be found every time. Simply sort out the functions in a unified manner and add the notebooks. There may be some omissions. Please add them. This article provides 30 time and date functions, which are divided into four sections for analysis. For more information, see the following section. 1. Obtain the Time Function

• Get current time
  • Sysdate ()
  • Now (), current_timestamp (), current_timestamp, LOCALTIME, LOCALTIME (), LOCALTIMESTAMP, LOCALTIMESTAMP ()
• Resting ns
  • Sleep (n)
• Example
  • Now (), current_timestamp (), current_timestamp, LOCALTIME, LOCALTIME (), LOCALTIMESTAMP, LOCALTIMESTAMP ()
    • Similarities: Get the current time
    • Difference: When is the return time, whether to execute the SQL statement or call the time value of the Function
      • Sysdate (), returns the time value of the function call time.
      • Now (), current_timestamp ()/current_timestamp, LOCALTIME, LOCALTIME (), LOCALTIMESTAMP, LOCALTIMESTAMP () return the time value of the statement call;
      • Clear the differences among the three items during use

2. Time computing functions

• 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
  • Datediff (date1, date2)
    • The two dates are subtracted from each other by the number of days. Note that the two dates must be in the same format.
  • Timediff (time1, time2)
    • The time difference between the two time subtraction. Note that the format must be the same.
  • TIMESTAMPDIFF (interval, datetime_expr1, datetime_expr2)
    • Difference between two times
    • Interval can be:
      • FRAC_SECOND millisecond, SECOND, MINUTE, HOUR
      • DAY, WEEK, MONTH, QUARTER, YEAR

• Date_sub (date, INTERVAL expr type)
  • Date minus the specified time interval.
  • If the expr value is positive, the time interval is subtracted. If the expr value is negative, the value is subtracted from the negative value, which is equal to the addition value.
  • The date parameter is a valid date expression. The expr parameter is the time interval you want to add. Type can be found below
  • @ Date = '2017-09-09 09:09:09'

  • Type Value
    MICROSECOND	10th second, ± n	Date_sub (@ date, INTERVAL 2 MICROSECOND)	08.999998
    SECOND	Seconds	Date_sub (@ date, INTERVAL 2 SECOND)	2017-09-09 09: 09:07
    MINUTE	Minutes	Date_sub (@ date, INTERVAL 2 MINUTE)	2017-09-09 09:07: 09
    HOUR	Hours	Date_sub (@ date, INTERVAL 2 HOUR)	2017-09-0907: 07: 09
    DAY	Days	Date_sub (@ date, INTERVAL 2 DAY)	2017-09-0709:09:09
    WEEK	Week	Date_sub (@ date, INTERVAL 2 WEEK)	2017-08-2609:09:09
    MONTH	Month	Date_sub (@ date, INTERVAL 2 MONTH)	2017-07-09 09:09:09
    QUARTER (QUARTER, 3 months)	Quarter	Date_sub (@ date, INTERVAL 2 QUARTER)	2017-03-09 09:09:09
    YEAR	Year	Date_sub (@ date, INTERVAL 2 YEAR)	2015-09:09:09, 09-09
    SECOND_MICROSECOND	Expression, from second to minute	Date_sub (@ date, INTERVAL '2. 2' SECOND_MICROSECOND)	2017-09-09 09: 09:06.980000
    MINUTE_MICROSECOND	Expression, from minute to second	Date_sub (@ date, INTERVAL '2: 2.2 'MINUTE_MICROSECOND)	2017-09-09 09:. 800000
    MINUTE_SECOND	Expression, from minute to second	Date_sub (@ date, INTERVAL '2: 2' MINUTE_SECOND)	2017-09-09 09:07:07
    HOUR_MICROSECOND	Expression, from hour to second	Date_sub (@ date, INTERVAL '2: 2: 2.2 'HOUR_MICROSECOND)	2017-09-0907:07:06. 800000
    HOUR_SECOND	Expression, from hour to second	Date_sub (@ date, INTERVAL '2: 2: 2' HOUR_SECOND)	2017-09-0907:07:07
    HOUR_MINUTE	Expression, from hour to minute	Date_sub (@ date, INTERVAL '2: 2' HOUR_MINUTE)	2017-09-0907:07: 09
    DAY_MICROSECOND	Expression, ranging from day to second,	Date_sub (@ date, INTERVAL '2. 2 'DAY_MICROSECOND)	2017-09-07 07:07:06. 800000
    DAY_SECOND	Expression, days to seconds,	Date_sub (@ date, INTERVAL '2 'DAY_SECOND)	2017-09-07 07:07:07
    DAY_MINUTE	Expression, days to minutes,	Date_sub (@ date, INTERVAL '2: 2 'DAY_MINUTE)	2017-09-07: 09
    DAY_HOUR	Expression, from day to hour,	Date_sub (@ date, INTERVAL '2 2' DAY_HOUR)	2017-09-07 07: 09: 09
    YEAR_MONTH	Expression, year to month,	Date_sub (@ date, INTERVAL '2 2' YEAR_MONTH)	2015-07-09 09:09:09

• DATE_ADD (date, INTERVAL expr type)
  • The specified interval for adding a date is used in the same way as date_sub.

• Adddate (date, INTERVAL expr type)
  • Adddate (date, INTERVAL expr type)
    • The specified interval for adding a date is the same as that for DATE_ADD.
  • Adddate (date, n)
    • Date added n days

• Timestampadd (interval, count, timestamp)
  • Time increase Function
    • Year
    • Quarter
    • Month
    • Day
    • Week
    • Hour
    • Minute
    • Second
    • Millisecond

3 time conversion functions

• From_unixtime (), unix_timestamp ()
  • The former converts the timestamp to the datetime format, and the latter converts the datetime format to the timestamp.

• Makedate (year, day_of_year), maketime (hour, minute, second)
  • Makedate is converted to a date based on the day in the year and year.
  • Maketime is converted to time by hour, minute, and second.

• Date (datetime), timestamp (date [, time])
  • The former converts the date and time format to the date format (only including year, month, and day), and the latter converts the date to the date and time format.

• Convert_tz (datetime, from_tz, to_tz)
  • Returns the time in the Time Zone B based on the time in the Time Zone.

• Date_format (datetime, format), str_to_date (string, format)
  • The former converts the date time to the text in the specified format, and the latter converts the text to the date time in the specified format.

• Example
  • From_unixtime
    • Converts a timestamp to a time stamp.
  • Unix_timestamp
    • Convert time to Timestamp
  • Makedate (year, day_of_year), maketime (hour, minute, second)
    • Makedate is converted to a date based on the day in the year and year.
    • Maketime is converted to time by hour, minute, and second.
  • Date (datetime)
    • Convert datetime format to datetime format
  • Timestamp (date), timestamp (date, time)
    • Timestamp to conversion
  • Convert_tz (datetime, from_tz, to_tz)
    • Time zone conversion
    • Datetime is the time zone of from_zt and returns the time of to_tz.
  • Str_to_date (string, format)
    • Convert string to date format. Which of the following strings are year, month, day, and time?
  • Date_format
    • Year
      • % X, where Sunday is the first day of the week, 4 digits, used with % V
      • % X, where Monday is the first day of the week, 4 digits, used with % v
      • % Y year, 4 digits
      • % Y years, two places
    • Month
      • % B abbreviated month name
      • % C month, numeric value
      • % M month name
      • % M month, value (00-12)
    • Day
      • % D the day of the month with an English prefix
      • Day of % d month, value (00-31)
      • % E day (0-31)
      • % J-day (001-366)
    • Time Module
      • % T time, 24-hour (hh: mm: ss)
      • % R time, 12-hour (hh: mm: ss AM or PM)
    • Hours
      • % H hour (00-23)
      • % K hour (0-23)
      • % H hour (01-12)
      • % I hour (01-12) # % h and % I, no difference (uppercase I)
      • % L hour (1-12) lower case L
      • % P AM or PM
    • Minutes
      • % I minutes, numeric value (00-59)
    • Seconds
      • % S seconds (00-59)
      • % S seconds (00-59)
      • % F microseconds
    • Week
      • % A abbreviated name for a week
      • % W week name, full name Saturday
      • % W days (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, used with % X
      • % V Week (01-53) Monday is the first day of the week, used with % x
  • • Practical Application
      • Select now (), DATE_FORMAT (NOW (), '% Y-% m-% D')' % Y-% m-% d ';

4. Obtain some functions

• Get the day
  • To_days (datetime), dayofyear (datetime), dayofmonth (datetime)
  • TodaysReturns the number of days between '2014-00-00 'and datetime.
  • DayofyearReturns the day of the year of datetime.
  • DayofmonthReturns the day of the month of datetime.
• Second Conversion
  • Time_to_sec (time), sec_to_time (number)
  • The former is converted to seconds based on time, and the latter is converted to time based on seconds.

  •