MySQL Date Function Guide with Examples

January 28, 2021

Home » SysAdmin » MySQL Date Function Guide with Examples

Introduction

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.

MySQL date functions guide with examples

Note: For more MySQL functions and commands, check out our MySQL Cheat Sheet.

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();

For example, if you run:

SELECT CURDATE();

MySQL responds with the current date in the format:

2021-01-17

DATE

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

The basic syntax:

DATE(datetime);

For instance, if you run:

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

The output is:

2021-01-17

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);

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);

The output returns the result:

2021-01-17 07:34:21

DATE_FORMAT

Format a date using DATE_FORMAT.

The basic syntax:

DATE_FORMAT(date, format);

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

Specifiers used to create date formats for MySQL DATE_FORMAT function.

For instance, if you run:

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

The output displays the result:

Sunday January 2021

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);

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);

The output returns the result:

2021-01-17 06:14:21

DATEDIFF

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

The basic syntax:

DATEDIFF(date1,date2);

For example:

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

Returns the result:

EXTRACT

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

The basic syntax:

EXTRACT(unit FROM date);

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');

You get the result:

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)

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.

MySQL GET_FORMAT 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'));

Where the result is:

26.01.2021

MAKEDATE

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

The basic syntax:

MAKEDATE(year,day);

For example, if you run:

SELECT MAKEDATE(2021,34);

The output shows the result:

2021-02-03

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);

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');

The output shows:

2021-01-25

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();

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();

At this time gives the result:

2021-01-25 20:21:04

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();

For example, running the following command:

SELECT UTC_DATE();

Returns the current date which is:

2021-01-25

Time-Related Functions

ADDTIME

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

The basic syntax:

ADDTIME(datetime, timevalue)

For instance, if you run:

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

You get the result:

2021-01-25 11:27:43.000027

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)

For example, when you run:

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

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

2021-01-25 20:12:00

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();

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);

The output responds with the result:

15:19:07.340

HOUR

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

The basic syntax:

HOUR(datetime);

For instance, if you run:

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

The result is:

MAKETIME

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

The basic syntax:

MAKETIME(hour, minute, second);

For example, you can run:

SELECT MAKETIME(09,25,00);

Where the output displays:

09:25:00

MICROSECOND

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

The basic syntax:

MICROSECOND(datetime);

For example, you can run:

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

Where the result is:

MINUTE

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

The basic syntax:

MINUTE(datetime);

For instance, if you run the command:

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

The result is:

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);

For instance, if you run the command:

SELECT SEC_TO_TIME(8897);

The output is:

02:28:17

SUBTIME

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

The basic syntax:

SUBTIME(datetime,timevalue);

For example, when you run:

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

The output is:

2021-01-21 19:03:59

TIME

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

The basic syntax:

TIME(datetime);

For instance:

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

Gives the result:

13:38:10

TIME_FORMAT

Format a time value into the specified format with TIME_FORMAT.

The basic syntax:

TIME_FORMAT(time,format);

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');

You get the output:

01 45 10 PM

TIME_TO_SEC

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

The basic syntax:

TIME_TO_SEC(timevalue);

For instance, when you run:

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

The result is:

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);

For instance, when you run:

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

The output returns:

240:00:01

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);

For example, if you run the command:

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

The result is:

63778435817

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();

For example, if you run:

SELECT UTC_TIME();

You get the result at this point of time is:

19:45:21

Note: Do you know how a traditional relational database compares to a document-oriented database management system? Learn more in this analysis of MySQL Vs. MongoDB.

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();

For example, by running:

SELECT CURRENT_TIMESTAMP();

The current result is:

2021-01-25 19:53:55

FROM_UNIXTIME

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

The basic syntax:

FROM_UNIXTIME(unix_timestamp);

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);

You get the result:

2021-01-21 12:16:44

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);

TIMESTAMP(datetime,time);

For example, when you run the command:

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

The output shows the result:

2021-01-14 06:50:00

TIMESTAMPADD

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

The basic syntax:

TIMESTAMPADD(unit,value,datetime);

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');

Therefore, the output shows:

2021-01-21

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);

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');

Where the result is:

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);

For instance, running the following command:

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

Gives the result:

1611595980

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);

For example, the command:

SELECT UTC_TIMESTAMP();

Returns output in the same format as the one below:

2021-01-25 23:18:06

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);

If you run the command below:

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

The output returns the result:

DAYNAME

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

The basic syntax:

DAYNAME(datetime);

For example, when you run the command:

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

MySQL responds with the result:

Tuesday

DAYOFMONTH

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

The basic syntax:

DAYOFMONTH(datetime);

For example, when you run the command:

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

MySQL responds with the result:

DAYOFWEEK

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

The basic syntax:

DAYOFWEEK(datetime);

Running the command below:

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

Gives the response:

DAYOFYEAR

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

The basic syntax:

DAYOFYEAR(datetime);

For instance, when you run the command:

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

The output gives the result:

FROM_DAYS

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

The basic syntax:

FROM_DAYS(number);

For example, running:

SELECT FROM_DAYS(738181);

The MySQL prompt responds with the result:

2021-01-26

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);

For instance, if you run the following command:

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

The output responds with:

MONTH

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

The basic syntax:

MONTH(date);

For example, when you run:

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

MySQL responds with:

MONTHNAME

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

The basic syntax:

MONTHNAME(date);

If you run the command:

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

You get the following response:

January

PERIOD_ADD

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

The basic syntax:

PERIOD_ADD(period,number);

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);

Gives the result:

PERIOD_DIFF

Return the number of months between two periods with PERIOD_DIFF .

The basic syntax:

PERIOD_DIFF(period1,period2);

Each period should be in the format YYMM or YYYYMM.

For instance:

SELECT PERIOD_DIFF(202101, 202003);

Produces the result:

QUARTER

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

The basic syntax:

QUARTER(date);

For example, by running the command:

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

The output responds with:

TO_DAYS

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

The basic syntax:

TO_DAYS(datetime);

For instance, for the command:

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

The result is :

WEEK

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

The basic syntax:

WEEK(date);

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');

The output responds with:

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);

For example, running the following command:

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

Gives the response:

WEEKOFYEAR

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

The basic syntax:

WEEKOFYEAR(date);

For instance, when you run the command:

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

The output shows the result:

YEAR

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

The basic syntax:

YEAR(date);

If you run the following command:

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

You get the result:

YEARWEEK

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

The basic syntax:

YEARWEEK(date);

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');

The output displays:

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.

Was this article helpful?

YesNo

Twitter Facebook LinkedIn Email

Sofija Simic

Sofija Simic is an experienced Technical Writer. Alongside her educational background in teaching and writing, she has had a lifelong passion for information technology. She is committed to unscrambling confusing IT concepts and streamlining intricate software installations.

Next you should read