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.
| Data Type | Explanation | Range |
| DATE | A date value (no time) | 1000-01-01 to 9999-12-31 |
| DATETIME (fsp) | A date and time value.
The parameter “fsp” is fractional seconds precision or the number of fractional seconds that can be stored |
1000-01-01 00:00:00.000000 to 9999-12-31 23:59:59.999999 |
| TIMESTAMP (fsp) | A timestamp value, stores date and time. Has a smaller range than DATETIME.
The parameter “fsp” is fractional seconds precision or the number of fractional seconds that can be stored, |
1970-01-01 00:00:01.000000 UTC to 2038-01-19 03:14:07.999999 |
| TIME (fsp) | A time value.
The parameter “fsp” is fractional seconds precision or the number of fractional seconds that can be stored, |
-838:59:59.000000 to 838:59:59.000000 |
| YEAR | A year in a 4-digit format. | 1901 to 2155 |
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:
| Value | Meaning |
| ‘2020-08-04 09:45:16+04:00’ | UTC + 4 hours |
| ‘2020-08-04 09:45:16-09:00’ | UTC – 9 hours |
| ‘2020-08-04 09:45:16+11:00’ | UTC + 11 hours |
| ‘2020-08-04 09:45:16-10:30’ | UTC – 10.5 hours |
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.
| Specifier | Description |
| %a | Abbreviated weekday name (Sun to Sat) |
| %b | Abbreviated month name (Jan to Dec) |
| %c | Month, in numeric form (0 to 12) |
| %D | Day of the month with an English suffix (0th, 1st, 2nd, 3rd, …) |
| %d | Day of the month, in numeric form (00 to 31) |
| %e | Day of the month, in numeric form (0 to 31) |
| %f | Microseconds (000000 to 999999) |
| %H | Hour (00 to 23) |
| %h | Hour (01 to 12) |
| %I | Hour (01 to 12) |
| %i | Minutes, in numeric form (00..59) |
| %j | Day of year (001 to 366) |
| %k | Hour (0 to 23) |
| %l | Hour (1 to 12) |
| %M | Full month name (January to December) |
| %m | Month, in numeric form (00 to 12) |
| %p | AM or PM |
| %r | Time, 12-hour (hh:mm:ss AM or PM) |
| %S | Seconds (00 to 59) |
| %s | Seconds (00 to 59) |
| %T | Time, 24-hour (hh:mm:ss) |
| %U | Week (00 to 53), where Sunday is the first day of the week |
| %u | Week (00 to 53), where Monday is the first day of the week |
| %V | Week (01 to 53), where Sunday is the first day of the week |
| %v | Week (01 to 53), where Monday is the first day of the week |
| %W | Weekday name (Sunday to Saturday) |
| %w | Day of the week (0=Sunday to 6=Saturday) |
| %X | Year for the week where Sunday is the first day of the week, numeric, four digits; used with %V |
| %x | Year for the week where Monday is the first day of the week, numeric, four digits; used with %v |
| %Y | Year, numeric, four digits |
| %y | Year, numeric, two digits |
| %% | A literal % character |
| %x | x, for any “x” not listed above |
Let’s take a look at the functions.
MySQL Date Functions
Here’s a list of all of the date functions in MySQL.
| Name | Description |
| ADDDATE() | Add time values to a date value |
| ADDTIME() | Add time to a date and time |
| CONVERT_TZ() | Convert a value from one time zone to another |
| CURDATE() | Return the current date |
| CURRENT_DATE() | Synonyms for CURDATE() |
| CURRENT_TIME() | Synonyms for CURTIME() |
| CURRENT_TIMESTAMP() | Synonyms for NOW() |
| CURTIME() | Return the current time |
| DATE() | Extract and return the date part of a date or datetime |
| DATE_ADD() | Add time values to a date value. Opposite to DATE_SUB |
| DATE_FORMAT() | Format a date as specified using format specifiers |
| DATE_SUB() | Subtract a time value from a date. Opposite to DATE_ADD |
| DATEDIFF() | Subtract two dates |
| DAY() | Synonym for DAYOFMONTH() |
| DAYNAME() | Return the name of the weekday |
| DAYOFMONTH() | Return the day of the month (0-31) |
| DAYOFWEEK() | Return the weekday index of the specified value |
| DAYOFYEAR() | Return the day of the year (1-366) |
| EXTRACT() | Extract a part of a date |
| FROM_DAYS() | Convert a day number to a date |
| FROM_UNIXTIME() | Format a Unix timestamp as a date |
| GET_FORMAT() | Return a date format string |
| HOUR() | Extract the hour of a specified time |
| LAST_DAY() | Return the last day of the month for the specified date |
| LOCALTIME() | Synonym for NOW() |
| LOCALTIMESTAMP() | Synonym for NOW() |
| MAKEDATE() | Create a date from the year and day of year |
| MAKETIME() | Create a time from a specified hour, minute, second |
| MICROSECOND() | Return the microseconds from the specified value |
| MINUTE() | Return the minute from the the specified value |
| MONTH() | Return the month from the date passed |
| MONTHNAME() | Return the name of the month |
| NOW() | Return the current date and time |
| PERIOD_ADD() | Add a period to a year-month |
| PERIOD_DIFF() | Return the number of months between periods |
| QUARTER() | Return the quarter from a date the specified value |
| SEC_TO_TIME() | Converts seconds to ‘hh:mm:ss’ format |
| SECOND() | Return the second (0-59) |
| STR_TO_DATE() | Convert a string to a date |
| SUBDATE() | Subtracts a number of days from a date. |
| SUBTIME() | Subtract a time from a datetime |
| SYSDATE() | Return the time that the function executes |
| TIME() | Extract the time portion of the expression passed |
| TIME_FORMAT() | Format as time |
| TIME_TO_SEC() | Return the specified value converted to seconds |
| TIMEDIFF() | Subtract time from two values |
| TIMESTAMP() | With a single argument, this function returns the date or datetime expression. With two arguments, the sum of the arguments |
| TIMESTAMPADD() | Add an interval to a datetime expression |
| TIMESTAMPDIFF() | Subtract an interval from a datetime expression |
| TO_DAYS() | Return the date argument converted to days |
| TO_SECONDS() | Return the date or datetime argument converted to seconds since Year 0 |
| UNIX_TIMESTAMP() | Return a Unix timestamp |
| UTC_DATE() | Return the current UTC date |
| UTC_TIME() | Return the current UTC time |
| UTC_TIMESTAMP() | Return the current UTC date and time |
| WEEK() | Return the week number |
| WEEKDAY() | Return the weekday index |
| WEEKOFYEAR() | Return the calendar week of the date (1-53) |
| YEAR() | Return the year |
| YEARWEEK() | Return the year and week |
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:
| Example | Result |
| GET_FORMAT(DATE, ‘EUR’) | %d.%m.%Y |
| GET_FORMAT(DATE, ‘USA’) | %m.%d.%Y |
| GET_FORMAT(DATE, ‘JIS’) | %Y-%m-%d |
| GET_FORMAT(DATE, ‘ISO’) | %Y-%m-%d |
| GET_FORMAT(DATE, ‘INTERNAL’) | %Y%m%d |
| GET_FORMAT(TIME, ‘EUR’) | %H.%i.%s |
| GET_FORMAT(TIME, ‘USA’) | %h:%i:%s %p |
| GET_FORMAT(TIME, ‘JIS’) | %H:%i:%s |
| GET_FORMAT(TIME, ‘ISO’) | %H:%i:%s |
| GET_FORMAT(TIME, ‘INTERNAL’) | %H%i%s |
| GET_FORMAT(DATETIME, ‘EUR’) | %Y-%m-%d %H.%i.%s |
| GET_FORMAT(DATETIME, ‘USA’) | %Y-%m-%d %H.%i.%s |
| GET_FORMAT(DATETIME, ‘JIS’) | %Y-%m-%d %H:%i:%s |
| GET_FORMAT(DATETIME, ‘ISO’) | %Y-%m-%d %H:%i:%s |
| GET_FORMAT(DATETIME, ‘INTERNAL’) | %Y%m%d%H%i%s |
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
SELECT
TIME_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.
SELECT
TIMEDIFF('2020-08-04 10:14:55', '2020-08-04 12:00:00');Result:
-1:45:05
SELECT
TIMEDIFF('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:
| Mode | Day Starts On | Range | Week 1 is the first week with |
| 0 | Sunday | 0-53 | a Sunday in this year |
| 1 | Monday | 0-53 | 4 or more days this year |
| 2 | Sunday | 1-53 | a Sunday in this year |
| 3 | Monday | 1-53 | 4 or more days this year |
| 4 | Sunday | 0-53 | 4 or more days this year |
| 5 | Monday | 0-53 | a Monday in this year |
| 6 | Sunday | 1-53 | 4 or more days this year |
| 7 | Monday | 1-53 | a Monday in this year |
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.