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
- CONVERT_TZ() – Convert a datetime value from one time zone to another.
- FROM_DAYS() – Convert a numeric day count into a date.
- STR_TO_DATE() – Convert a string to date.
- FROM_UNIXTIME() – Convert UNIX timestamps into a readable date and time format.
- UNIX_TIMESTAMP() – Convert a datetime to a UNIX timestamp.
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.
