MySQL Date Data Types and Date Functions

Working with dates is something you'll do a lot in MySQL.

It helps to understand the different date data types and many of the functions that can be used with them.

In this guide, we'll look at the MySQL date data types, the date functions, and see some examples of them.

MySQL Date Data Types

There are several data types in MySQL that can hold dates.

There are five data types in MySQL. However, two of them only store part of a date: TIME stores the time, and YEAR stores the year.

Let's focus on the data types that can store a date:

• DATE: used for values that contain a date but no time
• DATETIME: used for values that contain a date and time
• TIMESTAMP: used for values that contain a date and time

Interestingly, there are no timezone-specific data types in MySQL.

There are a few other interesting points to note about these data types.

DATETIME and TIMESTAMP can store fractional seconds up to 6 decimal places.

When displaying data in these data types, the formats of the display are:

• DATE: 'YYYY-MM-DD'
• DATETIME: 'YYYY-MM-DD hh:mm:ss[.fraction]'
• TIMESTAMP: 'YYYY-MM-DD hh:mm:ss[.fraction]'

Timezones in MySQL

MySQL will convert a TIMESTAMP value from the current time zone into UTC time zone when it's stored in the database and will convert it back to the current time zone when it's retrieved. This does not happen for DATETIME.

The timezone is determined by the server's time and can be set for a connection. You can check the timezone by looking up the time_zone system variable.

As of MySQL version 8.0.19, you can specify a timezone offset when you insert a DATETIME or TIMESTAMP value. You do this by adding the timezone offset to the end of the datetime value, without spaces:

If an hour value is less than 10 you need to specify a leading zero (e.g. 05 instead of 5). Unlike other databases, you can't use defined timezone names such as PST or US/Eastern.

You can use a range of offset values from -14:00 to +14:00.

MySQL Date Format

When working with dates, you often need to specify a format string (for example, when using the DATE_FORMAT function). The following table demonstrates the different characters and what they mean.

Let's take a look at the functions.

MySQL Date Functions

Here's a list of all of the date functions in MySQL.

Let's take a look at the syntax and some examples of these functions.

If you want to know how to get the current date in MySQL, read this post: How to Get the Current Date in SQL.

ADDDATE

The ADDDATE function is used to add a value to a date, and return a date. It can be used with two different types of parameters:

1ADDDATE(date, days)
2ADDDATE(date, INTERVAL expression unit)

If you use days as the second parameter, the function will add that number of days to the specified date.

1SELECT ADDDATE('2020-08-04', 10);

Result:

12020-08-14

If you use the INTERVAL as the second parameter, the function will add the specified number of whatever unit you have specified. This is also the same as the DATE_ADD function.

1SELECT ADDDATE('2020-08-04', INTERVAL 3 DAY);

Result:

12020-08-07

1SELECT ADDDATE('2020-08-04', INTERVAL 10 MONTH);

Result:

12021-06-04

ADDTIME()

The ADDTIME function will add both parameters and return the result. The syntax is:

1ADDTIME(expr1, expr2)

The first parameter is a datetime or time, and the second parameter is a time.

1SELECT ADDTIME('2020-08-04 00:00:00', '05:33:21');

Result:

12020-08-04 05:33:21

1SELECT ADDTIME('2020-08-04 06:25:10', '00:45:03');

Result:

12020-08-04 07:10:13

1SELECT ADDTIME('08:30:55', '07:41:16');

Result:

116:12:11

CONVERT_TZ()

The CONVERT_TZ function will convert a datetime value from a timezone into another timezone. The syntax is:

1CONVERT_TZ(date, from_timezone, to_timezone)

The timezone values can either be strings that represent a supported timezone (e.g. 'GMT') or a timezone offset (e.g. '+5:00').

1SELECT CONVERT_TZ('2020-08-04 10:03:21', 'GMT', 'US/Eastern');

Result:

12020-08-04 05:03:21

1SELECT CONVERT_TZ('2020-08-04 10:03:21', '+00:00', '+09:00');

Result:

12020-08-04 19:03:21

The named timezones (e.g. GMT) can only be used if the timezones are properly set up in your MySQL database.

CURDATE()

The CURDATE function returns the current date. It either returns a string in the format of 'YYYY-MM-DD' or a number in the format of YYYYMMDD.

1SELECT CURDATE();

Result:

12020-08-13

1SELECT CURDATE() + 0;

Result:

120200813

CURRENT_DATE()

The CURRENT_DATE function is a synonym for the CURDATE function mentioned above.

1SELECT CURRENT_DATE();

Result:

12020-08-13

CURRENT_TIME()

The CURRENT_TIME function is a synonym for the CURTIME function mentioned below.

1SELECT CURRENT_TIME();

Result:

106:09:17

CURRENT_TIMESTAMP()

The CURRENT_TIMESTAMP function is a synonym for the NOW function mentioned below.

1SELECT CURRENT_TIMESTAMP();

Result:

12020-08-13 06:09:17

CURTIME()

The CURTIME function returns the current time in the session time zone. It returns either a string in the format of 'hh:mm:ss' or a number in the format of hhmmss.

1SELECT CURTIME();

Result:

106:09:17

1SELECT CURTIME() + 0;

Result:

160917

DATE()

The DATE function extracts the date part of the specified datetime expression. It returns the date of a value that contains a date and time.

1SELECT DATE('2020-08-13 06:09:17');

Result:

12020-08-13

DATE_ADD()

The DATE_ADD function adds the specified interval and units to the specified date. It's the same as one version of the ADDDATE function.

1SELECT DATE_ADD('2020-08-04', INTERVAL 1 DAY);

Result:

12020-08-05

1SELECT DATE_ADD('2020-08-04', INTERVAL 3 MONTH);

Result:

12020-11-04

You can also use combined interval values, such as a combination of minutes and seconds.

1SELECT DATE_ADD('2020-08-04 10:21:07', INTERVAL '5:8' MINUTE_SECOND);

Result:

12020-08-04 10:26:15

DATE_FORMAT()

The DATE_FORMAT function formats the specified date according to the specified format string.

1DATE_FORMAT(date, format)

The full list of format strings is shown in the MySQL Date Format table earlier in this guide. You need to specify the % character before the format specifiers.

1SELECT DATE_FORMAT('2020-08-04', '%d/%m/%Y');

Result:

104/08/2020

1SELECT DATE_FORMAT('2020-08-04', '%a %d %b %Y');

Result:

1Tue 04 Aug 2020

1SELECT DATE_FORMAT('2020-08-04 10:14:54', '%d %m %Y %h:%i');

Result:

104 08 2020 10:14

DATE_SUB()

The DATE_SUB function will subtract the specified interval and units from the specified date. It's the opposite of the DATE_ADD function.

1SELECT DATE_SUB('2020-08-04', INTERVAL 1 DAY);

Result:

12020-08-03

1SELECT DATE_SUB('2020-08-04', INTERVAL 5 MONTH);

Result:

12020-03-04

You can also use combined interval values, such as a combination of minutes and seconds.

1SELECT DATE_SUB('2020-08-04 14:12:55', INTERVAL '8:20' MINUTE_SECOND);

Result:

12020-08-04 14:04:35

DATEDIFF()

The DATEDIFF function returns the number of days between the two specified dates: expression1 - expression2.

1DATEDIFF(expression1, expression2)

Here are some examples.

1SELECT DATEDIFF('2020-08-04', '2020-02-01');

Result:

1185

1SELECT DATEDIFF('2020-08-04', '2018-10-06');

Result:

1668

1SELECT DATEDIFF('2020-08-04', '2020-08-13');

Result:

1-9

1SELECT DATEDIFF('2020-08-04', CURDATE());

Result:

1-9

DAY()

The DAY function is a synonym for DAYOFMONTH. It returns the day of the month for the specified date, from 1 to 31.

1SELECT DAY('2020-08-04');

Result:

14

1SELECT DAY(CURDATE());

Result:

113

DAYNAME()

The DAYNAME function returns the name of the day of the week for the specified date.

1DAYNAME(date)

Here are some examples:

1SELECT DAYNAME('2020-08-04');

Result:

1Tuesday

1SELECT DAYNAME(CURDATE());

Result:

1Thursday

DAYOFMONTH()

The DAYOFMONTH function will return a number that represents the day of the month for the specified date, from 1 to 31.

1SELECT DAYOFMONTH('2020-08-04');

Result:

14

1SELECT DAYOFMONTH(CURDATE());

Result:

113

DAYOFWEEK()

The DAYOFWEEK function will return the current weekday number of the specified date. Day 1 is Sunday and day 7 is Saturday.

1SELECT DAYOFWEEK('2020-08-04');

Result:

13

1SELECT DAYOFWEEK(CURDATE());

Result:

15

DAYOFYEAR()

The DAYOFYEAR function returns the number of the day in the current year, from 1 to 366.

1SELECT DAYOFYEAR('2020-03-21');

Result:

181

1SELECT DAYOFYEAR('2020-08-04');

Result:

1217

EXTRACT()

The EXTRACT function will extract a part of a date from a specified date value.

1EXTRACT(unit FROM date)

It uses the same unit specifiers as other date functions such as DATE_ADD and DATE_SUB.

1SELECT EXTRACT(DAY FROM '2020-08-04');

Result:

14

1SELECT EXTRACT(MONTH FROM '2020-08-04');

Result:

18

1SELECT EXTRACT(YEAR FROM '2020-08-04');

Result:

12020

1SELECT EXTRACT(YEAR_MONTH FROM '2020-08-04');

Result:

1202008

FROM_DAYS()

The FROM_DAYS function will convert a specified number to a date value.

1SELECT FROM_DAYS(737951);

Result:

12020-06-10

1SELECT FROM_DAYS(730000);

Result:

11998-09-03

FROM_UNIXTIME()

The FROM_UNIXTIME function will convert a specified Unix timestamp number value into a datetime.

1FROM_UNIXTIME(unix_timestamp[, format])

Here are some examples:

1SELECT FROM_UNIXTIME(1447876598);

Result:

12015-11-19 06:56:38

1SELECT FROM_UNIXTIME(1447876598, '%d/%m/%Y %h:%i:%s');

Result:

119/11/2015 06:56:38

GET_FORMAT()

The GET_FORMAT function will return a format string. It's helpful when used with the DATE_FORMAT and STR_TO_DATE functions.

The syntax is:

1GET_FORMAT({DATE|TIME|DATETIME}, {'EUR'|'USA'|'JIS'|'ISO'|'INTERNAL'})

As you can see, there are only a few specific ways you can use this function. Each of them can be run to return a specific result.

The region values (the second parameter) mean:

• EUR: European format
• USA: United States format
• JIS: Japanese format
• ISO: Format for ISO 9075.
• INTERNAL: The default internal format

Here's what each of them returns:

HOUR()

The HOUR function returns the hour number for the specified time. It can be greater than 24.

1SELECT HOUR('14:20:45');

Result

114

1SELECT HOUR('301:16:12');

Result:

1301

LAST_DAY()

The LAST_DAY function will return a date that represents the last day of the same month of the specified value.

If you provide a date, this function returns the last day of that month.

1SELECT LAST_DAY('2020-08-04');

Result:

12020-08-31

1SELECT LAST_DAY('2019-02-20');

Result:

12019-02-28

LOCALTIME()

The LOCALTIME function is a synonym of the NOW function, and will return the current date and time in either a 'YYYY-MM-DD hh:mm:ss' or YYYYMMDDhhmmss format.

1SELECT LOCALTIME();

Result:

12020-08-13 07:22:44

1SELECT LOCALTIME() + 0;

Result:

120200813072244

LOCALTIMESTAMP()

The LOCALTIMESTAMP function is a synonym of the NOW function, and will return the current date and time in either a 'YYYY-MM-DD hh:mm:ss' or YYYYMMDDhhmmss format.

1SELECT LOCALTIMESTAMP();

Result:

12020-08-13 07:22:44

1SELECT LOCALTIMESTAMP() + 0;

Result:

120200813072244

MAKEDATE()

The MAKEDATE function will return a date using the specified year and day of year values.

The syntax is:

1MAKEDATE(year, dayofyear)

Here are some examples:

1SELECT MAKEDATE(2020, 1);

Result:

12020-01-01

1SELECT MAKEDATE(2020, 21);

Result:

12020-01-21

1SELECT MAKEDATE(2020, 30);

Result:

12020-01-30

1SELECT MAKEDATE(2020, 40);

Result:

12020-02-09

1SELECT MAKEDATE(2020, 185);

Result:

12020-07-03

MAKETIME()

The MAKETIME function will return a time value from the specified hour, minute, and second values.

The syntax is:

1MAKETIME(hour, minute, second)

Here are some examples:

1SELECT MAKETIME(12, 18, 44);

Result:

112:18:44

1SELECT MAKETIME(3, 19, 50);

Result:

103:19:50

MICROSECOND()

The MICROSECOND function will return the number of microseconds from the provided time or datetime.

1SELECT MICROSECOND('2020-08-04 10:12:53.9876');

Result:

1987600

MINUTE()

The MINUTE function will return the minute from the specified time value.

1SELECT MINUTE('10:45:03');

Result:

145

1SELECT MINUTE('2020-08-04 10:12:53');

Result:

112

MONTH()

The MONTH function will return the month of the specified date.

1SELECT MONTH(CURDATE());

Result:

18

1SELECT MONTH('2020-08-04');

Result:

18

1SELECT MONTH('2020-03-21');

Result:

13

MONTHNAME()

The MONTHNAME function will return the full name of the month of the specified date.

1SELECT MONTHNAME('2020-08-04');

Result:

1August

1SELECT MONTHNAME('2020-03-21');

Result:

1March

NOW()

The NOW function will return the current date and time in either the 'YYYY-MM-DD hh:mm:ss' format or YYYYMMDDhhmmss format. Fractional seconds can also be specified.

1SELECT NOW();

Result:

12020-08-13 09:20:17

1SELECT NOW() + 0;

Result:

120200813092017

1SELECT NOW(3);

Result:

12020-08-13 09:20:17.818

PERIOD_ADD()

The PERIOD_ADD function lets you add a specific number of months to a "period" argument, which represents a year and month and is in the format of YYMM or YYYYMM.

1PERIOD_ADD(period, number_months)

The value returned is in the format of YYYYMM.

1SELECT PERIOD_ADD(202004, 4);

Result:

1202008

1SELECT PERIOD_ADD(201909, 6);

Result:

1202003

PERIOD_DIFF()

The PERIOD_DIFF function returns the number of months between the two specified periods. These periods should be in the format of YYYYMM or YYMM. If period1 is larger than (occurs after) period2, then the result is negative.

1PERIOD_DIFF(period1, period2)

Here are some examples:

1SELECT PERIOD_DIFF(202001, 202004);

Result:

1-3

1SELECT PERIOD_DIFF(202006, 201906);

Result:

112

QUARTER()

The QUARTER function will return the quarter number for the date provided. The quarter is a number from 1 to 4.

1SELECT QUARTER('2020-01-03');

Result:

11

1SELECT QUARTER('2020-06-20');

Result:

12

1SELECT QUARTER('2020-08-04');

Result:

13

SEC_TO_TIME()

The SEC_TO_TIME function converts the provided number of seconds to a time value.

1SELECT SEC_TO_TIME(2819);

Result:

100:46:59

1SELECT SEC_TO_TIME(10615);

Result:

102:56:55

SECOND()

The SECOND function returns the second value from a specified time.

1SELECT SECOND('10:45:03');

Result:

103

1SELECT SECOND('2020-08-04 10:12:53');

Result:

153

STR_TO_DATE()

The STR_TO_DATE function will convert the specified string using the specified format into a datetime value.

1STR_TO_DATE(string, format)

It's the opposite of the DATE_FORMAT function.

1SELECT STR_TO_DATE('20200804', '%Y%m%d');

Result:

12020-08-04

1SELECT STR_TO_DATE('21/03/2020', '%d/%m/%Y');

Result:

12020-03-21

1SELECT STR_TO_DATE('June 5, 2020 09:45:20', '%M %d, %Y %h:%i:%s');

Result:

12020-06-05 09:45:20

SUBDATE()

The SUBDATE function will subtract a unit of time from a specified date. It's similar to the DATE_SUB function when used with a number of days.

There are two ways to use this function:

1SUBDATE(date, INTERVAL expression unit)
2SUBDATE(expression, days)

Let's see some examples

1SELECT SUBDATE('2020-08-04', 9);

Result:

12020-07-26

1SELECT SUBDATE('2020-08-04', INTERVAL 2 DAY);

Result:

12020-08-02

1SELECT SUBDATE('2020-08-04', INTERVAL 14 MONTH);

Result:

12019-06-04

SUBTIME()

The SUBTIME function will subtract one expression from another, where the first expression is a time or datetime and the second expression is a time.

1SELECT SUBTIME('2020-08-04 10:14:51', '04:10:06');

Result:

12020-08-04 06:04:45

1SELECT SUBTIME('2020-08-04 10:14:51', '1 16:45:02');

Result:

12020-08-02 17:29:49

SYSDATE()

The SYSDATE function returns the current date and time, in the format of 'YYYY-MM-DD hh:mm:ss'. An optional number of fractional seconds can be specified.

1SELECT SYSDATE();

Result:

12020-08-13 10:41:11

1SELECT SYSDATE(4);

Result:

12020-08-13 10:41:11.9207

TIME()

The TIME function will extract the time part of a time or datetime and return it as a string.

1SELECT TIME('2020-08-04 06:15:41');

Result:

106:15:41

1SELECT TIME(SYSDATE());

Result:

110:42:17

TIME_FORMAT()

The TIME_FORMAT function will format a specified time value into a specified format. It's similar to the DATE_FORMAT but the format string can only include specifiers for hours, minutes, seconds, and microseconds.

1SELECT TIME_FORMAT('04:06:12', '%h %i %s');

Result:

104 06 12

1SELECT
2TIME_FORMAT('17:31:14', '%h hours, %i minutes, %s seconds');

Result:

105 hours, 31 minutes, 14 seconds

TIME_TO_SEC()

The TIME_TO_SEC function will return the specified time value converted to a number of seconds.

1SELECT TIME_TO_SEC('04:06:12');

Result:

114772

1SELECT TIME_TO_SEC('17:31:14');

Result:

163074

TIMEDIFF()

The TIMEDIFF function will return a time that is the difference of the two provided expressions. The specified expressions must both be the same type, either time or datetime.

1SELECT
2TIMEDIFF('2020-08-04 10:14:55', '2020-08-04 12:00:00');

Result:

1-1:45:05

1SELECT
2TIMEDIFF('2020-08-04 06:18:41', '2020-08-01 19:21:04');

Result:

158:57:37

TIMESTAMP()

The TIMESTAMP function will do two things. With one parameter, it will return the parameter as a datetime value. With two parameters, it will add the second time parameter to the first parameter and return a datetime value.

1SELECT TIMESTAMP('2020-08-04');

Result:

12020-08-04 00:00:00

1SELECT TIMESTAMP('2020-08-04', '06:14:10');

Result:

12020-08-04 06:14:10

1SELECT TIMESTAMP('2020-08-04 03:00:00', '06:14:10');

Result:

12020-08-04 09:14:10

TIMESTAMPADD()

The TIMESTAMPADD function will add the specified number of units of an interval to a specified date.

The syntax looks like this:

1TIMESTAMPADD(unit, interval, datetime)

Let's see some examples:

1SELECT TIMESTAMPADD(MINUTE, 5, '2020-08-04');

Result:

12020-08-04 00:05:00

1SELECT TIMESTAMPADD(HOUR, 4, '2020-08-04');

Result:

12020-08-04 04:00:00

1SELECT TIMESTAMPADD(WEEK, 2, '2020-08-04');

Result:

12020-08-18

TIMESTAMPDIFF()

The TIMESTAMPDIFF function will return the difference between two datetimes in the specified units.

The syntax is:

1TIMESTAMPDIFF(unit, datetime1, datetime2)

Let's see some examples:

1SELECT TIMESTAMPDIFF(MONTH, '2020-08-04', '2020-10-15')

Result:

12

1SELECT TIMESTAMPDIFF(DAY, '2020-08-04', '2020-11-20');

Result:

1108

1SELECT TIMESTAMPDIFF(SECOND, '2020-08-04 10:45:18', '2020-11-20 09:06:55');

Result:

19325297

TO_DAYS()

The TO_DAYS function will return the number of days since year 0 for the specified date.

1SELECT TO_DAYS('2020-08-04');

Result:

1738006

1SELECT TO_DAYS('1976-10-09');

Result:

1722001

TO_SECONDS()

The TO_SECONDS function will return the number of seconds since the year 0 for the specified date.

1SELECT TO_SECONDS('2020-08-04');

Result:

163763718400

1SELECT TO_SECONDS('1976-10-09');

Result:

162380886400

UNIX_TIMESTAMP()

The UNIX_TIMESTAMP function will do one of two things.

If no parameters are specified, it will return the number of seconds since '1970-01-01 00:00:00'.

If one parameter is specified, it will return the number of seconds of that date since '1970-01-01 00:00:00'.

1SELECT UNIX_TIMESTAMP();

Result:

11597280572

1SELECT UNIX_TIMESTAMP('2020-08-04');

Result:

11596463200

UTC_DATE()

The UTC_DATE function will return the current UTC date as a value in either 'YYYY-MM-DD' format or YYYYMMDD format.

1SELECT UTC_DATE();

Result:

12020-08-13

1SELECT UTC_DATE() + 0;

Result:

120200813

UTC_TIME()

The UTC_TIME function will return the current UTC time in either 'hh:mm:ss' format or hhmmss format. If a fractional seconds parameter is specified, it will return the fractional seconds to that position.

1SELECT UTC_TIME();

Result:

101:04:48

1SELECT UTC_TIME() + 0;

Result:

110448

1SELECT UTC_TIME(3);

Result:

101:04:48.667

UTC_TIMESTAMP()

The UTC_TIMESTAMP function will return the current UTC date and time in either the 'YYYY-MM-DD hh:mm:ss' format or YYYYMMDDhhmmss format.

If a fractional seconds parameter is specified, it will return the fractional seconds to that position.

1SELECT UTC_TIMESTAMP();

Result:

12020-08-13 01:06:14

1SELECT UTC_TIMESTAMP() + 0;

Result:

120200813010614

1SELECT UTC_TIMESTAMP(3);

Result:

12020-08-13 01:06:14.824

WEEK()

The WEEK function returns the week number of the specified date.

1WEEK(date[, mode])

You can add an optional mode parameter, which specifies which day the week starts on, which number the week starts with, and how the first week is structured.

Here's how the mode parameter works:

Here are some examples:

1SELECT WEEK('2020-08-04');

Result:

131

1SELECT WEEK('2020-08-04', 0);

Result:

131

1SELECT WEEK('2020-08-04', 1);

Result:

132

1SELECT WEEK('2020-08-04', 2);

Result:

131

WEEKDAY()

The WEEKDAY function returns the weekday for the specified date, where 0 is Monday and 6 is Sunday.

1SELECT WEEKDAY('2020-08-04');

Result:

11

1SELECT WEEKDAY('2020-08-15');

Result:

15

WEEKOFYEAR()

The WEEKOFYEAR function returns the calendar week of the date. It's equivalent to WEEK using mode 3.

1SELECT WEEKOFYEAR('2020-08-04');

Result:

132

1SELECT WEEKOFYEAR('2020-01-15');

Result:

13

YEAR()

The YEAR function will return the year of the specified date.

1SELECT YEAR('2020-08-04');

Result:

12020

1SELECT YEAR('2003-12-18');

Result:

12003

YEARWEEK()

The YEARWEEK function returns the year and week for a specified date. A second parameter, called mode, can be specified, and it works the same way as the WEEK function.

1SELECT YEARWEEK('2020-08-04');

Result:

1202031

1SELECT YEARWEEK('2020-01-15');

Result:

1202002

1SELECT YEARWEEK('2020-01-15', 3);

Result:

1202003

Conclusion

As you can see, MySQL has quite a lot of date functions. They should let you do anything you need with dates, either by themselves or in combination with each other.