Skip to content

Latest commit

 

History

History
85 lines (61 loc) · 7.09 KB

MySQL Date Functions.md

File metadata and controls

85 lines (61 loc) · 7.09 KB

原文地址：https://www.mysqltutorial.org/mysql-date-functions/

MySQL Date Functions

This page shows you the most commonly used MySQL Date functions that allow you to manipulate date and time data effectively.

Section 1. Getting the current Date & Time

This section explains the functions that allow you to retrieve the current date, time, or both.

  • CURDATE() – Return the current date. ( synonyms: CURRENT_DATE() & CURRENT_DATE).
  • CURRENT_TIME – Return the current time ( synonyms: CURRENT_TIME() & CURTIME() ).
  • NOW() – Return the current date and time ( synonyms: CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP, LOCALTIME(), LOCALTIMESTAMP()).
  • SYSDATE() – Return the time at which it executes.
  • UTC_TIMESTAMP() – Return the current UTC date and time.
  • UTC_DATE() – Return the current UTC date.
  • UTC_TIME() – Return the current UTC time.

Section 2. Calculating Date and Time

  • ADDTIME() – Add a time interval to a time value or datetime value.
  • DATE_ADD() – Add a time value to a date (synonyms: ADDDATE()).
  • DATE_SUB() – Subtract a time value (interval) from a date.
  • DATEDIFF() – Return the difference in days of two date values.
  • TIMEDIFF() – Return the difference of two time values.
  • TIMESTAMPADD() – Add or subtract an interval from a timestamp or date.
  • TIMESTAMPDIFF() – Return the difference between two timestamp values.
  • TIME_TO_SEC() – Return the number of seconds from a time argument.
  • TO_DAYS() – Return a day number (the number of days since year 0) from a given date.

Section 3. Converting Functions

Section 4. Formatting Date & Time functions

  • DATE_FORMAT() – Return a string representation of a date based on a format.
  • TIME_FORMAT() – Return a string representation of a time based on a format.
  • GET_FORMAT() – Return a format string for a date, time, datetime, or timestamp.

Section 5. Extracting Date & Time Functions

The extraction functions allow you to extract date and time components from a date and time.

  • DATE() – Extract the date component from a date.
  • EXTRACT() – Extract a component of a date.
  • YEAR() – Return the year component of a date.
  • YEARWEEK() – Return the year and week for a date.
  • QUARTER() – Return the quarter of the year for a date.
  • MONTH() – Return the month component of a date.
  • WEEK() – Return the week component of a date.
  • WEEKDAY() – Return the weekday index of a date.
  • WEEKOFYEAR() – Return the calendar week of the date (1-53) – equivalent to WEEK(date, 3).
  • DAY() – Return the day of the month for a specific date (1-31). DAYOFMONTH is the synonym for DAY.
  • DAYOFYEAR() – Return the day of the year (1-366).
  • DAYOFWEEK() – Return the day of the week (1-7).
  • HOUR() – Return the hour for a time.
  • MINUTE() – Return the minute for a time.
  • SECOND() – Return the second for a time.
  • LAST_DAY() – Return an integer that represents the last day of the month for a specific date.

Section 6. Getting Month & Day Names

This section shows you how to use functions to get the month and day names.

  • DAYNAME() – Return the name of the day for a specific date.
  • MONTHNAME() – Return the name of the month for a specific date.

Section 7. Creating Date & Time Functions

  • MAKEDATE() – create a date based on a given year and the number of days.
  • MAKETIME() – create a time based on hour, minute, and second.

Section 8. Handling Period Functions

This section covers the function that manipulates the periods in the format YYMM or YYMMMM.

  • PERIOD_ADD() – add a number of months to a period in the format YYMM or YYMMMM.
  • PERIOD_DIFF() – calculate the difference in months of two periods represented in the format YYMM or YYYYMM.