MySQL Date Data Types and Date Functions (2024)

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:

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

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

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

Result:

2020-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.

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

Result:

2020-08-07

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

Result:

2021-06-04

ADDTIME()

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

ADDTIME(expr1, expr2)

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

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

Result:

2020-08-04 05:33:21

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

Result:

2020-08-04 07:10:13

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

Result:

16:12:11

CONVERT_TZ()

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

CONVERT_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’).

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

Result:

2020-08-04 05:03:21

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

Result:

2020-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.

SELECT CURDATE();

Result:

2020-08-13

SELECT CURDATE() + 0;

Result:

20200813

CURRENT_DATE()

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

SELECT CURRENT_DATE();

Result:

2020-08-13

CURRENT_TIME()

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

SELECT CURRENT_TIME();

Result:

06:09:17

CURRENT_TIMESTAMP()

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

SELECT CURRENT_TIMESTAMP();

Result:

2020-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.

SELECT CURTIME();

Result:

06:09:17

SELECT CURTIME() + 0;

Result:

60917

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.

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

Result:

2020-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.

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

Result:

2020-08-05

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

Result:

2020-11-04

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

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

Result:

2020-08-04 10:26:15

DATE_FORMAT()

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

DATE_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.

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

Result:

04/08/2020

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

Result:

Tue 04 Aug 2020

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

Result:

04 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.

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

Result:

2020-08-03

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

Result:

2020-03-04

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

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

Result:

2020-08-04 14:04:35

DATEDIFF()

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

DATEDIFF(expression1, expression2)

Here are some examples.

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

Result:

185

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

Result:

668

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

Result:

-9

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

Result:

-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.

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

Result:

4

SELECT DAY(CURDATE());

Result:

13

DAYNAME()

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

DAYNAME(date)

Here are some examples:

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

Result:

Tuesday

SELECT DAYNAME(CURDATE());

Result:

Thursday

DAYOFMONTH()

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

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

Result:

4

SELECT DAYOFMONTH(CURDATE());

Result:

13

DAYOFWEEK()

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

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

Result:

3

SELECT DAYOFWEEK(CURDATE());

Result:

5

DAYOFYEAR()

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

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

Result:

81

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

Result:

217

EXTRACT()

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

EXTRACT(unit FROM date)

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

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

Result:

4

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

Result:

8

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

Result:

2020

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

Result:

202008

FROM_DAYS()

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

SELECT FROM_DAYS(737951);

Result:

2020-06-10

SELECT FROM_DAYS(730000);

Result:

1998-09-03

FROM_UNIXTIME()

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

FROM_UNIXTIME(unix_timestamp[, format])

Here are some examples:

SELECT FROM_UNIXTIME(1447876598);

Result:

2015-11-19 06:56:38

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

Result:

19/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:

GET_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.

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

Result

14

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

Result:

301

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.

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

Result:

2020-08-31

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

Result:

2019-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.

SELECT LOCALTIME();

Result:

2020-08-13 07:22:44

SELECT LOCALTIME() + 0;

Result:

20200813072244

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.

SELECT LOCALTIMESTAMP();

Result:

2020-08-13 07:22:44

SELECT LOCALTIMESTAMP() + 0;

Result:

20200813072244

MAKEDATE()

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

The syntax is:

MAKEDATE(year, dayofyear)

Here are some examples:

SELECT MAKEDATE(2020, 1);

Result:

2020-01-01

SELECT MAKEDATE(2020, 21);

Result:

2020-01-21

SELECT MAKEDATE(2020, 30);

Result:

2020-01-30

SELECT MAKEDATE(2020, 40);

Result:

2020-02-09

SELECT MAKEDATE(2020, 185);

Result:

2020-07-03

MAKETIME()

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

The syntax is:

MAKETIME(hour, minute, second)

Here are some examples:

SELECT MAKETIME(12, 18, 44);

Result:

12:18:44

SELECT MAKETIME(3, 19, 50);

Result:

03:19:50

MICROSECOND()

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

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

Result:

987600

MINUTE()

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

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

Result:

45

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

Result:

12

MONTH()

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

SELECT MONTH(CURDATE());

Result:

8

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

Result:

8

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

Result:

3

MONTHNAME()

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

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

Result:

August

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

Result:

March

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.

SELECT NOW();

Result:

2020-08-13 09:20:17

SELECT NOW() + 0;

Result:

20200813092017

SELECT NOW(3);

Result:

2020-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.

PERIOD_ADD(period, number_months)

The value returned is in the format of YYYYMM.

SELECT PERIOD_ADD(202004, 4);

Result:

202008

SELECT PERIOD_ADD(201909, 6);

Result:

202003

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.

PERIOD_DIFF(period1, period2)

Here are some examples:

SELECT PERIOD_DIFF(202001, 202004);

Result:

-3

SELECT PERIOD_DIFF(202006, 201906);

Result:

12

QUARTER()

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

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

Result:

1

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

Result:

2

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

Result:

3

SEC_TO_TIME()

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

SELECT SEC_TO_TIME(2819);

Result:

00:46:59

SELECT SEC_TO_TIME(10615);

Result:

02:56:55

SECOND()

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

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

Result:

03

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

Result:

53

STR_TO_DATE()

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

STR_TO_DATE(string, format)

It’s the opposite of the DATE_FORMAT function.

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

Result:

2020-08-04

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

Result:

2020-03-21

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

Result:

2020-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:

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

Let’s see some examples

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

Result:

2020-07-26

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

Result:

2020-08-02

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

Result:

2019-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.

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

Result:

2020-08-04 06:04:45

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

Result:

2020-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.

SELECT SYSDATE();

Result:

2020-08-13 10:41:11

SELECT SYSDATE(4);

Result:

2020-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.

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

Result:

06:15:41

SELECT TIME(SYSDATE());

Result:

10: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.

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

Result:

04 06 12

SELECTTIME_FORMAT('17:31:14', '%h hours, %i minutes, %s seconds');

Result:

05 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.

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

Result:

14772

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

Result:

63074

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.

SELECTTIMEDIFF('2020-08-04 10:14:55', '2020-08-04 12:00:00');

Result:

-1:45:05

SELECTTIMEDIFF('2020-08-04 06:18:41', '2020-08-01 19:21:04');

Result:

58: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.

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

Result:

2020-08-04 00:00:00

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

Result:

2020-08-04 06:14:10

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

Result:

2020-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:

TIMESTAMPADD(unit, interval, datetime)

Let’s see some examples:

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

Result:

2020-08-04 00:05:00

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

Result:

2020-08-04 04:00:00

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

Result:

2020-08-18

TIMESTAMPDIFF()

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

The syntax is:

TIMESTAMPDIFF(unit, datetime1, datetime2)

Let’s see some examples:

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

Result:

2

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

Result:

108

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

Result:

9325297

TO_DAYS()

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

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

Result:

738006

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

Result:

722001

TO_SECONDS()

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

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

Result:

63763718400

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

Result:

62380886400

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’.

SELECT UNIX_TIMESTAMP();

Result:

1597280572

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

Result:

1596463200

UTC_DATE()

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

SELECT UTC_DATE();

Result:

2020-08-13

SELECT UTC_DATE() + 0;

Result:

20200813

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.

SELECT UTC_TIME();

Result:

01:04:48

SELECT UTC_TIME() + 0;

Result:

10448

SELECT UTC_TIME(3);

Result:

01: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.

SELECT UTC_TIMESTAMP();

Result:

2020-08-13 01:06:14

SELECT UTC_TIMESTAMP() + 0;

Result:

20200813010614

SELECT UTC_TIMESTAMP(3);

Result:

2020-08-13 01:06:14.824

WEEK()

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

WEEK(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:

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

Result:

31

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

Result:

31

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

Result:

32

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

Result:

31

WEEKDAY()

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

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

Result:

1

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

Result:

5

WEEKOFYEAR()

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

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

Result:

32

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

Result:

3

YEAR()

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

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

Result:

2020

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

Result:

2003

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.

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

Result:

202031

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

Result:

202002

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

Result:

202003

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.