FB

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.

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.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.