MySQL provides the various date and Time functions to work with Dates, such as Finding a current date, Time, format, extracting date part, time part, etc.
MySQL Date Functions
The following is the list of MySQL Date functions that are available to work with Date and time. You can click each MySQL Date and Time function to see details about the function with multiple examples. Or, scroll down to see an example of each Date and Time function.
| Functions | Description |
|---|---|
| ADDDATE() | Add Date Value in intervals to a given Date. |
| ADDTIME() | This adds Time |
| CONVERT_TZ() | It converts to date and time from one time zone to another. |
| CURDATE() | Returns the Current date |
| CURRENT_DATE, CURRENT_DATE() | Synonym of CURDATE() |
| CURRENT_TIME(), CURRENT_TIME | Synonym of CURTIME() function |
| CURRENT_TIMESTAMP() ,CURRENT_TIMESTAMP | Synonym of NOW() function |
| CURTIME() | This MySQL function returns the current Time |
| DATE() | It extracts the date part from the Given Date or DateTime expression |
| DATE_ADD() | It adds given intervals to the date expression |
| DATE_FORMAT() | This method formats the Date as per our requirements. |
| DATE_SUB() | This subtracts the specified intervals from the given date. |
| DATEDIFF() | Subtracts one date from another and returns the difference. |
| DAY() | Synonym of DAYOFMONTH() function. |
| DAYNAME() | This MySQL date function returns the name of the Weekday. For example Monday |
| DAYOFMONTH() | It returns the day number of the Month from 1 to 31 |
| DAYOFWEEK() | This returns the Weekday index of the user’s given date. |
| DAYOFYEAR() | Returns the Day of the Year or Day number, i.e., 1 to 366 |
| EXTRACT() | Use this to extract part of a date. |
| FROM_DAYS() | This function converts a given day number to a date. |
| FROM_UNIXTIME() | Used to format Unix times-tamp as a Date |
| GET_FORMAT() | This returns the date format string. |
| HOUR() | Returns the Hour value from a given Time. |
| LAST_DAY | It returns on the last Day of the Month on a given date. |
| LOCALTIME(), LOCALTIME | Synonym of NOW() function |
| LOCALTIMESTAMP(), LOCALTIMESTAMP | Synonym of NOW() function |
| MAKEDATE() | It is used to create or make a date from the specified Year and day of the year. |
| MAKETIME() | This will create or make time from Hour, Minute, Second |
| MICROSECOND() | Returns the Microseconds from the given Time or DateTime. |
| MINUTE() | This function returns the Minutes value from the given Time or DateTime. |
| MONTH() | It retunes the Month Number (1 – 12) |
| MONTHNAME() | Returns the Month name or Name of the Month (January, February, etc.) |
| NOW() | This MySQL Date function returns the Current Date and Time. |
| PERIOD_ADD() | It adds the user specified period to a year-Month |
| PERIOD_DIFF() | Returns the difference between the two periods. It returns the number of months between those two periods. |
| QUARTER() | It finds and returns which quarter does this date belongs to |
| SEC_TO_TIME() | Converts the given seconds to Time of HH:MM:SS format. |
| SECOND() | Returns the seconds value (0-59) from a given Time. |
| STR_TO_DATE() | This function converts String to date. |
| SUBDATE() | Synonym of DATE_SUB() function |
| SUBTIME() | Subtract the interval from Time. |
| SYSDATE() | Returns the current system date and time at which the function executed |
| TIME() | This MySQL date function extracts the time portion from given DateTime or expression. |
| TIME_FORMAT() | Use this to format the given expression as time. |
| TIME_TO_SEC() | This method converts the given time to second. |
| TIMEDIFF() | It subtracts time. |
| TIMESTAMP() | It returns the Date or DateTime expression. |
| TIMESTAMPADD() | This adds an interval to a DateTime expression |
| TIMESTAMPDIFF() | This function subtracts an interval from the given DateTime expression. |
| TO_DAYS() | Converts the given date into a total number of Days. |
| TO_SECONDS() | Converts the given date or DateTime expression to seconds since Year 0. |
| UNIX_TimeStamp | Used to return the Unix Time stamp |
| UTC_Date | It returns the current UTC Date |
| UTC_Time | This method returns the current UTC Time |
| UTC_TimeStamp | Returns the current UTC Date and Time. |
| Week | This method returns the Week Number |
| Weekday | Returns the Weekday Index |
| WeekOfYear | It returns the Calendar Week of the Date. It ranges from 1 to 53 |
| Year | This returns the Year Value. |
| YearWeek | Returns the Year and Week |
Temporal Intervals of Date and Time Functions in MySQL
The following are the list of available temporal interval expressions and unit values.
| UNIT VALUE | Expected Expression Format |
|---|---|
| MICROSECOND | MICROSECONDS |
| SECOND | SECONDS |
| MINUTE | MINUTES |
| HOUR | HOURS |
| DAY | DAYS |
| WEEK | WEEKS |
| MONTH | MONTHS |
| QUARTER | QUARTERS |
| YEAR | YEARS |
| SECOND_MICROSECOND | ‘SECONDS.MICROSECONDS’ |
| MINUTE_MICROSECOND | ‘MINUTES:SECONDS.MICROSECONDS’ |
| MINUTE_SECOND | ‘MINUTES:SECONDS’ |
| HOUR_MICROSECOND | ‘HOURS:MINUTES:SECONDS.MICROSECONDS’ |
| HOUR_SECOND | ‘HOURS:MINUTES:SECONDS’ |
| HOUR_MINUTE | ‘HOURS:MINUTES’ |
| DAY_MICROSECOND | ‘DAYS HOURS:MINUTES:SECONDS.MICROSECONDS’ |
| DAY_SECOND | ‘DAYS HOURS:MINUTES:SECONDS’ |
| DAY_MINUTE | ‘DAYS HOURS:MINUTES’ |
| DAY_HOUR | ‘DAYS HOURS’ |
| YEAR_MONTH | ‘YEARS-MONTHS’ |
MySQL Date and Time Functions Examples
The following examples help you to understand these Date and Time functions in MySQL.
Example
In this example, we are going to use the ADDDATE and ADDTIME to add the given values. Next, we used CONVERT_TZ to convert the date from one standard to another.
SELECT ADDDATE('2016-01-02', INTERVAL 2 MONTH), ADDDATE('2018-02-01', INTERVAL 31 DAY);
SELECT ADDTIME('2018-12-31 23:55:57', '2 2:22:22');
SELECT CONVERT_TZ('2018-12-31 23:59:59', '+00:00', '-05:30');
CURDATE and CURRENT_DATE Example 2
In this example, we are going to use the CURDATE(), CURRENT_DATE, and CURRENT_DATE() methods to find the current date.
SELECT CURDATE(), CURDATE() + 5;
SELECT CURRENT_DATE, CURRENT_DATE + 2;
SELECT CURRENT_DATE(), CURRENT_DATE() + 10;
Time Functions Example 3
In this example, we are going to use the CURTIME, CURRENT_TIME(), CURRENT_TIME to find the current time.
SELECT CURTIME(), CURTIME() + 1;
SELECT CURRENT_TIME(), CURRENT_TIME() + 10;
SELECT CURRENT_TIME, CURRENT_TIME + 2;
In this MySQL Date functions example, we are going to use the DATE to get the date and DATEDIFF to find the difference between two dates. Next, we used the DATE_ADD to add date intervals.
SELECT DATE('2018-12-31 12:59:59'), DATE('2012-11-18 11:22:33');
SELECT DATEDIFF('2018-12-31', '2019-02-03'), DATEDIFF('2018-12-01', '2018-02-01');
SELECT DATE_ADD('2018-12-31', INTERVAL 2 DAY), DATE_ADD('2018-12-31', INTERVAL 2 YEAR);
Example 5
In this example, we are going to use the DATE_FORMAT to format the given one. DATE_SUB is to subtract the intervals from it. Next, we used the DAY to return day value.
SELECT DATE_FORMAT('2018-12-31 23:59:59', '%W, %D %M %Y');
SELECT DATE_SUB('2018-12-31', INTERVAL 12 DAY), DATE_SUB('2018-12-31', INTERVAL 2 YEAR);
SELECT DAY('2018-12-31'), DAY('2018-01-01');
In this date functions example, we are going to use the DAYNAME and DAYOFMONTH methods to get the name of the day, Day number of the Month. Next, we used DAYOFWEEK to get the weekday index number.
SELECT DAYNAME('2018-12-31'), DAYNAME('2019-01-01');
SELECT DAYOFMONTH('2018-12-14'), DAYOFMONTH('2019-01-31');
SELECT DAYOFWEEK('2018-12-14'), DAYOFWEEK('2019-01-31');
MySQL Date Functions Example 7
Here, we use the EXTRACT to extract date parts from the date. FROM_DAYS to return date from given days. FROM_UNIXTIME to format Unix timestamp as date and time.
SELECT EXTRACT(YEAR FROM '2019-07-02'), EXTRACT(MONTH FROM '2018-12-31');
SELECT FROM_DAYS(100000), FROM_DAYS(950010);
SELECT FROM_UNIXTIME(122227899), FROM_UNIXTIME(1233456789);
In this example, we are going to use the HOUR to get the hour value, LAST_DAY, to get last day in a month. Here, GET_FORMAT has to use along with DATE_FORMAT or STR_TO_DATE.
SELECT DATE_FORMAT('2018-12-31', GET_FORMAT(DATE, 'EUR'));
SELECT HOUR('12:22:33'), HOUR('23:59:59'), HOUR('2019-01-02 17:12:13');
SELECT LAST_DAY('2018-04-12'), LAST_DAY('2018-02-05');
MySQL Date Functions Example 9
In this example, we are going to use the LOCALTIME, LOCALTIME(), and LOCALTIMESTAMP to get the current date and time.
SELECT LOCALTIME, LOCALTIME + 1;
SELECT LOCALTIME(), LOCALTIME() + 15;
SELECT LOCALTIMESTAMP, LOCALTIMESTAMP + 4;
LOCALTIMESTAMP and CURRENT_TIMESTAMP Example 10
In this example, we used the LOCALTIMESTAMP(), CURRENT_TIMESTAMP, and CURRENT_TIMESTAMP() methods to get the current Date and Time.
SELECT LOCALTIMESTAMP(), LOCALTIMESTAMP() + 10;
SELECT CURRENT_TIMESTAMP, CURRENT_TIMESTAMP + 10;
SELECT CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP() + 10;
MySQL Date Functions Example 11
In this example, we used the MAKEDATE to create a date from Year and Day of Year. Next, the MAKETIME to create time from Hour, Minute, and Second. Then, we extract or return the Micro-second value using the MICROSECOND.
SELECT MAKEDATE(2018, 28), MAKEDATE(2018, 364);
SELECT MAKETIME(10, 11, 22), MAKETIME(23, 59, 58);
SELECT MICROSECOND('10:22:33.324567'), MICROSECOND('19:11:22.112233');
MINUTE AND MONTH NAME Example 12
Here, we are going to use the MINUTE to get the Minute value. Next, we used MONTH and MONTHNAME to get the Month Number and Month Name (name of the month).
SELECT MINUTE('10:22:33.324567'), MINUTE('2018-02-04 19:11:22');
SELECT MONTH('2018-12-31'), MONTH('2018-02-04 19:11:22');
SELECT MONTHNAME('2018-12-31'), MONTHNAME('2018-01-04 19:11:22');
MySQL Date Functions Example 13
In this example, we are going to use the NOW to get the current date and time. Next, we used PERIOD_ADD and PERIOD_DIFF to add months and to find the difference between two periods.
SELECT NOW(), NOW() + 5;
SELECT PERIOD_ADD(201801, 3), PERIOD_ADD(201802, 10);
SELECT PERIOD_DIFF(201812, 201907), PERIOD_DIFF(201812, 201802);
Second, sec_to_time, and str_to_date Example 14
In this example, we are going to use SECOND to get seconds and SEC_TO_TIME to convert the given second to time. Next, we used STR_TO_DATE to convert string to date.
SELECT SECOND('10:22:33.324567'), SECOND('2018-02-04 19:11:22');
SELECT SEC_TO_TIME(3652), SEC_TO_TIME(9875);
SELECT STR_TO_DATE('April 1, 2018', '%M %d, %Y');
SUBDATE, SUBTIME, and SYSDATE Example
In this example, we are going to use SUBDATE and SUBTIME to subtract date or time intervals from the given. Next, we sued the SYSDATE to return the System datetime.
SELECT SUBDATE('2018-12-31', INTERVAL 15 DAY), SUBDATE('2018-12-31', INTERVAL 2 YEAR);
SELECT SUBTIME('2018-12-31 10:11:12', '3 13:48:47');
SELECT SYSDATE(), SYSDATE() + 2;
TIME, TIMEDIFF, and TIMESTAMP Example 16
In this example, we are going to use the TIME to get Time and the TIMEDIFF to find the difference between two-time values. Next, we used the TIMESTAMP to return the DateTime.
SELECT TIME('2018-01-31 10:22:33.324567'), TIME('2018-02-04 19:11:22');
SELECT TIMEDIFF('2018-12-31 23:59:59.111222', '2018-12-30 01:10:10.333444');
SELECT TIMESTAMP('2018-12-31'), TIMESTAMP('2018-12-31 23:59:59');
MySQL Time Functions Example 17
In this example, we are going to use the TIMESTAMPADD to add intervals to date and TIMESTAMPDIFF to find the difference between the two timestamps. Next, we used the TIME_FORMAT to format the given time.
SELECT TIMESTAMPADD(HOUR, 13, '2018-01-31 10:22:33'), TIMESTAMPADD(MINUTE, 48, '2018-02-04 19:11:22');
SELECT TIMESTAMPDIFF(MONTH, '2018-01-31', '2018-12-30'), TIMESTAMPDIFF(MINUTE,'2018-02-04', '2018-02-04 19:11:22');
SELECT TIME_FORMAT('4:12:13', '%k %i %S');
TIME_TO_SECONDS and TO_DAYS Example
In this MySQL Date function example, we are going to use the TIME_TO_SECONDS to convert a given time to a number of seconds. Next, we used TO_DAYS to retune days.
SELECT TIME_FORMAT('4:12:13', '%k %i %S');
SELECT TIME_TO_SEC('23:59:59'), TIME_TO_SEC('10:10:00');
SELECT TO_DAYS('1947-08-15'), TO_DAYS('2018-12-31');
Here, we used the TO_SECONDS to convert the date to seconds and the UNIX_TIMESTAMP to return the Unix timestamp. Next, we used UTC_DATE to get the UTC date value.
SELECT TO_SECONDS('1947-08-15'), TO_SECONDS('2018-12-31');
SELECT UNIX_TIMESTAMP(), UNIX_TIMESTAMP('2018-12-31 23:59:59');
SELECT UTC_DATE, UTC_DATE();
WEEK, UTC_TIME, and UTC_TIMESTAMP Example 20
We use the UTC_TIME to get Time and UTC_TIMESTAMP to find the UTC DateTime. Next, we used the WEEK to get the Week number.
SELECT UTC_TIME, UTC_TIME();
SELECT UTC_TIMESTAMP, UTC_TIMESTAMP();
SELECT WEEK('2018-12-31'), WEEK('2018-12-31', 1);
WEEKDAY, WEEKOFYEAR, and YEAR Example 21
In this example, we are going to use the WEEKDAY to get the index number, WEEKOFYEAR to find the calendar week. Next, we used the YEAR to get the year value from the given.
SELECT WEEKDAY('2018-03-31'), WEEKDAY('2018-12-04 23:59:59');
SELECT WEEKOFYEAR('2018-12-31'), WEEKOFYEAR('2018-12-04 23:59:59');
SELECT YEAR('1947-08-15'), YEAR('2018-12-31');
In this example, we use the YEARWEEK function to return the year and week from a given one.
SELECT YEARWEEK('1947-08-15'), YEARWEEK('2018-12-31');
