MySQL Date Function Guide with Examples

MySQL comes with many built-in functions that allow you to manipulate data. These functions are grouped into categories – date functions, string functions, mathematic functions, and others.

Date functions give you numerous options on how to modify, calculate, and convert date, time, and datetime expressions in MySQL.

In this tutorial, you will learn about MySQL date and time functions and how they work, on practical examples.

Date-Related Functions

CURDATE OR CURRENT_DATE

Return the current date in the “YYY-MM-DD” or “YYYYMMDD” format with the CURDATE OR CURRENT_DATE command.

The basic syntax:

CURDATE();Copy

For example, if you run:

SELECT CURDATE();Copy

MySQL responds with the current date in the format:

2021-01-17Copy

DATE

Return the date from a datetime expression using the DATE command.

The basic syntax:

DATE(datetime);Copy

For instance, if you run:

SELECT DATE('2021-01-17 10:12:16');Copy

The output is:

2021-01-17Copy

DATE_ADD or ADDDATE

Add a time/date value to a date expression with the DATE_ADD or ADDDATE function.

The basic syntax:

DATE_ADD(date, INTERVAL value unit);Copy

Replace date with the date expression you want to add a time/date to. The value unit is the time/date you want to add. It needs to be expressed in a numeric value along with the unit of the value.

The unit can be:

• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• MONTH
• QUARTER
• YEAR
• SECOND_MICROSECOND
• MINUTE_MICROSECOND
• MINUTE_SECOND
• HOUR_MICROSECOND
• HOUR_SECOND
• HOUR_MINUTE
• DAY_MICROSECOND
• DAY_SECOND
• DAY_MINUTE
• DAY_HOUR
• YEAR_MONTH

For example, if you run:

SELECT DATE_ADD('2021-01-17 07:14:21', INTERVAL 20 MINUTE);Copy

The output returns the result:

2021-01-17 07:34:21Copy

DATE_FORMAT

Format a date using DATE_FORMAT.

The basic syntax:

DATE_FORMAT(date, format);Copy

The date is the date expression you want to reformat, while the format is a combination of the following specifiers:

For instance, if you run:

SELECT DATE_FORMAT('2021-01-17', '%W %M %Y');Copy

The output displays the result:

Sunday January 2021Copy

DATE_SUB or SUBDATE

Subtract a time/date value to a date expression with the DATE_SUB or SUBDATE function.

The basic syntax:

DATE_SUB(date, INTERVAL value unit);Copy

Replace date with the date expression you want to subtract from. The value unit is the time/date you want to subtract. It needs to be expressed in numeric value along with the unit of the value.

Find a list of unit types in the DATE_ADD section.

For instance, if you run:

SELECT DATE_SUB('2021-01-17 07:14:21', INTERVAL 1 HOUR);Copy

The output returns the result:

2021-01-17 06:14:21Copy

DATEDIFF

Return the number of days between two date expressions with the DATEDIFF function.

The basic syntax:

DATEDIFF(date1,date2);Copy

For example:

SELECT DATEDIFF('2021-01-23','2021-01-14');Copy

Returns the result:

9Copy

EXTRACT

To extract part of a date/datetime expression, use the EXTRACT function.

The basic syntax:

EXTRACT(unit FROM date);Copy

In the command, you need to specify which unit you want to extract from the specified date.

Find a list of units you can use in the DATE_ADD description.

For instance, when you run:

SELECT EXTRACT(DAY FROM '2021-01-26');Copy

You get the result:

26Copy

GET_FORMAT

Return a format string (a combination of specifiers) as specified in the argument with GET_FORMAT. This function is often used with DATE_FORMAT.

The basic syntax:

GET_FORMAT(DATE/TIME/DATETIME,format)Copy

Use this function with date, time, and datetime expressions.

The format can be:

• ‘EUR’
• ‘USA’
• ’JIS’
• ’ISO’
• ‘INTERNAL’

There is a finite number of results you can get using the GET_FORMAT function. Below you will find a list of all the function calls and their results.

For instance, you could combine the function with DATE_FORMAT, as in the following example:

SELECT DATE_FORMAT('2021-01-26', GET_FORMAT(DATE,'EUR'));Copy

Where the result is:

26.01.2021Copy

MAKEDATE

Return a date expression from a specified year and day of year using the MAKEDATEfunction.

The basic syntax:

MAKEDATE(year,day);Copy

For example, if you run:

SELECT MAKEDATE(2021,34);Copy

The output shows the result:

2021-02-03Copy

STR_TO_DATE

Format a date from a string with STR_TO_DATE and return a date/datetime value.

The basic syntax:

STR_TO_DATE(string, format);Copy

The string is what you want to reformat, while the format is a combination of specifiers that describe each element of the string.

You can find a list of specifiers and their meaning in the DATE_FORMAT section.

For instance, if you run:

SELECT STR_TO_DATE('January,25,2021', '%M %e %Y');Copy

The output shows:

2021-01-25Copy

SYSDATE

To return the current date and time in the format “YYYY-MM-DD hh:mm:ss” or “YYYYMMDDHHMMSS.uuuuuu”, use the SYSDATE function.

The basic syntax:

SYSDATE();Copy

You can add the fspargument to include fractional second precision (0-6). In that case, the syntax is SYSDATE(fsp);.

The command shown below:

SELECT SYSDATE();Copy

At this time gives the result:

2021-01-25 20:21:04Copy

UTC_DATE

Return the current Coordinated Universal Time (UTC) date value in the “YYYY-MM-DD” or “YYYYMMDD” format with the UTC_DATE function.

The basic syntax:

UTC_DATE();Copy

For example, running the following command:

SELECT UTC_DATE();Copy

Returns the current date which is:

2021-01-25Copy

Time-Related Functions

ADDTIME

Add a time interval to a specified time/datetime expression using ADDTIME.

The basic syntax:

ADDTIME(datetime, timevalue)Copy

For instance, if you run:

SELECT ADDTIME('2021-01-25 08:13:11.000021', '3:14:32.000006');Copy

You get the result:

2021-01-25 11:27:43.000027Copy

CONVERT_TZ

Convert a time/datetime expression from one time zone to another using the ADDTIME function.

The basic syntax:

CONVERT_TZ(datetime, from_timezone,to_timezone)Copy

For example, when you run:

SELECT CONVERT_TZ('2021-01-25 10:12:00','+00:00','+10:00');Copy

MySQL converts the specified datetime to +10:00 time zone:

2021-01-25 20:12:00Copy

CURTIME or CURRENT_TIME

Return the current time using the CURTIME or CURRENT_TIME function. The result returns the time in the “hh:mm:ss” or “hhmmss” format.

The basic syntax:

CURTIME();Copy

You can include fractional second precision (from 0 to 6) by adding the fsp argument.

For example, the following command shows the current time with three fractional second precision:

CURTIME(3);Copy

The output responds with the result:

15:19:07.340Copy

HOUR

Return the hour of the specified time/datetime with the HOUR function.

The basic syntax:

HOUR(datetime);Copy

For instance, if you run:

SELECT HOUR('08:40:07');Copy

The result is:

8Copy

MAKETIME

Return a time expression from the specified hour, minute, and second values using the MAKETIME function.

The basic syntax:

MAKETIME(hour, minute, second);Copy

For example, you can run:

SELECT MAKETIME(09,25,00);Copy

Where the output displays:

09:25:00Copy

MICROSECOND

Return the microseconds of the specified time/datetime expression with MICROSECOND.

The basic syntax:

MICROSECOND(datetime);Copy

For example, you can run:

SELECT MICROSECOND('2021-01-21 10:23:44.000040');Copy

Where the result is:

40Copy

MINUTE

Return the minutes of the specified time/datetime expression using the MINUTE function.

The basic syntax:

MINUTE(datetime);Copy

For instance, if you run the command:

SELECT MINUTE('10:23:44');Copy

The result is:

23Copy

SEC_TO_TIME

Return a time value from a specified seconds value with the SEC_TO_TIME function.

The basic syntax:

SEC_TO_TIME(seconds);Copy

For instance, if you run the command:

SELECT SEC_TO_TIME(8897);Copy

The output is:

02:28:17Copy

SUBTIME

Subtract a time value from a time/datetime expression using the SUBTIME function.

The basic syntax:

SUBTIME(datetime,timevalue);Copy

For example, when you run:

SELECT SUBTIME('2021-01-21 21:24:00','2:20:1');Copy

The output is:

2021-01-21 19:03:59Copy

TIME

To return the time value from a datetime expression, use the TIME function.

The basic syntax:

TIME(datetime);Copy

For instance:

SELECT TIME('2021-01-22 13:38:10');Copy

Gives the result:

13:38:10Copy

TIME_FORMAT

Format a time value into the specified format with TIME_FORMAT.

The basic syntax:

TIME_FORMAT(time,format);Copy

The format is a combination of specifiers. You can find a list of all specifiers and their meaning in the description of the DATE_FORMAT function.

For example, by running:

SELECT TIME_FORMAT('13:45:10','%h %i %s %p');Copy

You get the output:

01 45 10 PMCopy

TIME_TO_SEC

To return the time value converted into seconds use the TIME_TO_SEC.

The basic syntax:

TIME_TO_SEC(timevalue);Copy

For instance, when you run:

SELECT TIME_TO_SEC('13:48:05');Copy

The result is:

49685Copy

TIMEDIFF

Calculate the difference between two time/datetime expressions with the TIMEDIFF function. In this case, the result is always in time value.

The basic syntax:

TIMEDIFF(datetime1,datetime2);Copy

For instance, when you run:

SELECT TIMEDIFF('2021-01-15 11:10:17','2021-01-05 11:10:16');Copy

The output returns:

240:00:01Copy

TO_SECONDS

To convert a date/datetime expression into seconds, use the function TO_SECONDS. The result is the number of seconds between 0 and the specified date/datetime.

The basic syntax:

TO_SECONDS(datetime);Copy

For example, if you run the command:

SELECT TO_SECONDS('2021-01-21 08:10:17');Copy

The result is:

63778435817Copy

UTC_TIME

Return the current UTC time value with UTC_TIME. It returns the time value in the “HH:MM:SS” or “HHMMSS” format.

The basic syntax:

UTC_TIME();Copy

For example, if you run:

SELECT UTC_TIME();Copy

You get the result at this point of time is:

19:45:21Copy

Timestamp-Related Functions

CURRENT_TIMESTAMP or LOCALTIMESTAMP

To return the current date and time, use CURRENT_TIMESTAMP or LOCALTIMESTAMP. The result returns in the “YYYY-MM-DD HH-MM-SS” or “YYYYMMDDHHMMSS.uuuuuu” format.

The basic syntax:

CURRENT_TIMESTAMP();Copy

For example, by running:

SELECT CURRENT_TIMESTAMP();Copy

The current result is:

2021-01-25 19:53:55Copy

FROM_UNIXTIME

Return a date/datetime expression from a timestamp in the Unix format with FROM_UNIXTIME.

The basic syntax:

FROM_UNIXTIME(unix_timestamp);Copy

If you run the command without an argument specifying the format, it returns the result in the “YYYY-MM-DD hh:mm:ss” or “YYYYMMDDhhmmss” format.

For instance, if you run:

SELECT FROM_UNIXTIME(1611231404);Copy

You get the result:

2021-01-21 12:16:44Copy

TIMESTAMP

To return a datetime expression from a date or datetime value, use the function TIMESTAMP. If you add two arguments, the output returns the sum of the arguments.

The basic syntax:

TIMESTAMP(datetime);Copy

TIMESTAMP(datetime,time);Copy

For example, when you run the command:

SELECT TIMESTAMP('2021-01-13','30:50:00');Copy

The output shows the result:

2021-01-14 06:50:00Copy

TIMESTAMPADD

Add a time value to a date/datetime expression using the TIMESTAMPADD function.

The basic syntax:

TIMESTAMPADD(unit,value,datetime);Copy

The unit can be:

• FRAC_SECOND
• SECOND
• MINUTE
• HOUR
• DAY
• WEEK
• MONTH
• QUARTER
• YEAR

For instance, the following command adds 3 days to the specified date:

SELECT TIMESTAMPADD(DAY,3,'2021-01-18');Copy

Therefore, the output shows:

2021-01-21Copy

TIMESTAMPDIFF

To calculate the difference between two date/datetime expressions, use TIMESTAMPDIFF. The function subtracts one datetime value from the other in the specified unit.

The basic syntax:

TIMESTAMPDIFF(unit,datetime1,datetime2);Copy

You can find a list with different types of units, check out the list in the section above.

For example, you can calculate the difference between the following two dates in days:

SELECT TIMESTAMPDIFF(DAY,3,'2021-01-18');Copy

Where the result is:

13Copy

UNIX_TIMESTAMP

Return a Unix timestamp from a date/datetime expression with the UNIX_TIMESTAMP function. The Unix timestamp represents seconds between the specified datetime and “1970-01-01 00:00:00” UTC.

The basic syntax:

UNIX_TIMESTAMP(datetime);Copy

For instance, running the following command:

SELECT UNIX_TIMESTAMP('2021-01-25 17:33:00');Copy

Gives the result:

1611595980Copy

UTC_TIMESTAMP

Return the current UTC date and time value with UTC_TIMESTAMP. It returns the datetime value in the “YYYY-MM-DD HH:MM:SS” or “YYYYMMDDHHMMSS.uuuuuu” format.

The basic syntax:

UTC_TIMESTAMP(datetime);Copy

For example, the command:

SELECT UTC_TIMESTAMP();Copy

Returns output in the same format as the one below:

2021-01-25 23:18:06Copy

Day/Week/Month/Year-Related Functions

DAY

Return the day of a month from a specified date/datetime expression with the DAY function.

The basic syntax:

DAY(datetime);Copy

If you run the command below:

SELECT DAY('2021-01-26 12:32:00');Copy

The output returns the result:

26Copy

DAYNAME

Return the name of the weekday from a specified date/datetime expression using the DAYNAME function.

The basic syntax:

DAYNAME(datetime);Copy

For example, when you run the command:

SELECT DAYNAME('2021-01-26 12:32:00');Copy

MySQL responds with the result:

TuesdayCopy

DAYOFMONTH

Return the day of a month from a specified date/datetime expression with DAYOFMONTH.

The basic syntax:

DAYOFMONTH(datetime);Copy

For example, when you run the command:

SELECT DAYOFMONTH('2021-01-26 12:32:00');Copy

MySQL responds with the result:

26Copy

DAYOFWEEK

Return the day of week in numerical value from the specified date/datetime expression using DAYOFWEEK.

The basic syntax:

DAYOFWEEK(datetime);Copy

Running the command below:

SELECT DAYOFWEEK('2021-01-26 12:32:00');Copy

Gives the response:

3Copy

DAYOFYEAR

Return the day in a year from the specified date/datetime expression using the function DAYOFYEAR.

The basic syntax:

DAYOFYEAR(datetime);Copy

For instance, when you run the command:

SELECT DAYOFYEAR('2021-02-26 12:32:00');Copy

The output gives the result:

57Copy

FROM_DAYS

Return a date expression from a numeric representation of a day using the function FROM_DAYS.

The basic syntax:

FROM_DAYS(number);Copy

For example, running:

SELECT FROM_DAYS(738181);Copy

The MySQL prompt responds with the result:

2021-01-26Copy

LAST_DAY

Return the last day of the month from a specified date/datetime with the LAST_DAY function.

The basic syntax:

LAST_DAY(date);Copy

For instance, if you run the following command:

SELECT LAST_DAY('2021-01-26');Copy

The output responds with:

31Copy

MONTH

Return the month (in numeric value) from a specified date/datetime by using the MONTH function.

The basic syntax:

MONTH(date);Copy

For example, when you run:

SELECT MONTH('2021-01-26');Copy

MySQL responds with:

1Copy

MONTHNAME

Return the name of the month from a specified date/datetime with the MONTHNAME function.

The basic syntax:

MONTHNAME(date);Copy

If you run the command:

SELECT MONTH('2021-01-26');Copy

You get the following response:

JanuaryCopy

PERIOD_ADD

To add a specified number of months to a period, use the PERIOD_ADD function.

The basic syntax:

PERIOD_ADD(period,number);Copy

The period is defined in the format YYMM or YYYYMM, while the number is the number of months you want to add.

For example:

SELECT PERIOD_ADD(202101, 5);Copy

Gives the result:

202106Copy

PERIOD_DIFF

Return the number of months between two periods with PERIOD_DIFF .

The basic syntax:

PERIOD_DIFF(period1,period2);Copy

Each period should be in the format YYMM or YYYYMM.

For instance:

SELECT PERIOD_DIFF(202101, 202003);Copy

Produces the result:

10Copy

QUARTER

To return a quarter of a year from a specified date/datetime, use the function QUARTER.

The basic syntax:

QUARTER(date);Copy

For example, by running the command:

SELECT QUARTER('2021-01-26');Copy

The output responds with:

1Copy

TO_DAYS

Convert a date/datetime expression to a numeric representation of a day with TO_DAYS.

The basic syntax:

TO_DAYS(datetime);Copy

For instance, for the command:

SELECT TO_DAYS('2021-01-26');Copy

The result is :

738181Copy

WEEK

To return the week number from a specified date, use the function WEEK.

The basic syntax:

WEEK(date);Copy

You can also include the mode argument, in which case the syntax is WEEK(date,mode);.

The mode argument specifies from which day the week starts. If there is no argument, it uses 0 mode by default.

The following table describes each mode:

For example, if you run:

SELECT WEEK('2021-01-26');Copy

The output responds with:

4Copy

WEEKDAY

Return the weekday from the specified date in numeric value with WEEKDAY. Each number represents one of the weekdays – Monday is 0, Tuesday is 1, and so on.

The basic syntax:

WEEKDAY(date);Copy

For example, running the following command:

SELECT WEEKDAY('2021-01-26');Copy

Gives the response:

1Copy

WEEKOFYEAR

To return the number of a week in a year, use the WEEKDAY function.

The basic syntax:

WEEKOFYEAR(date);Copy

For instance, when you run the command:

SELECT WEEKOFYEAR('2021-01-26');Copy

The output shows the result:

3Copy

YEAR

Return the year from the specified date with the YEAR function.

The basic syntax:

YEAR(date);Copy

If you run the following command:

SELECT YEAR('2021-01-26');Copy

You get the result:

2021Copy

YEARWEEK

Return the year and week number from the specified date using the YEARWEEK function.

The basic syntax:

YEARWEEK(date);Copy

You can add a mode argument to the basic syntax to specify the day from which the week begins. To see a list of modes and their meaning, check out the table in the WEEK function.

For example, when you run the command:

SELECT YEARWEEK('2021-01-26');Copy

The output displays:

202104Copy

Conclusion

This article should help you find all the MySQL date (and time) functions. With descriptions for each function and practical examples, you should be able to apply them with ease.

To have a better understanding of different data types, we suggest to check out our article on MySQL Data Types.