MySQL DATETIME data type and DATETIME functions

1. DATETIME data type

DATETIME type is one of five temporary datatypes for date and time values, along with TIME, DATE, TIMESTAMP, and YEAR.

DATETIME type is used to store values that contain both date and time parts. MySQL retrieves and displays DATETIME values in ’YYYY-MM-DD hh:mm:ss’ format. The supported range is ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.

The DATETIME values can include a trailing fractional seconds part in up to microseconds (6 digits) precision.

Dates containing two-digit year values are ambiguous because the century is unknown. MySQL interprets two-digit year values using these rules:

Year values in the range 00-69 are converted to 2000-2069.
Year values in the range 70-99 are converted to 1970-1999.

2. DATETIME formats

MySQL accept DATETIME values with these formats:

As a string in either ‘YYYY-MM-DD hh:mm:ss’ or ‘YY-MM-DD hh:mm:ss’ format.
- Any punctuation character may be used as the delimiter between date parts or time parts. For example, you can replace the hyphen (-) with (+), (:), (^), (*), (@), etc.
- The date and time parts can be separated by character T or a space. For example, ‘2019-10-07T01:20:30’ or ‘2019-10-07 01:20:30’.
- The only delimiter recognized between a date and time part and a fractional seconds part is the decimal point.
As a string with no delimiters in either ‘YYYYMMDDhhmmss’ or ‘YYMMDDhhmmss’ format
As a number in either YYYYMMDDhhmmss or YYMMDDhhmmss format

Invalid DATETIME values will be converted to the zero value formatted as ‘0000-00-00 00:00:00’ if permitted by the SQL mode.

3. MySQL DATETIME functions

Here are some commonly used functions to work with DATETIME values:

DATE() function to get the date value from a date time value:

SELECT DATE('2019-10-07 01:20:30');

The result is “2019-10-07”

NOW() function to return the current date and time, at the time of query execution.

SELECT NOW();

The result is “2019-10-07 12:08:31”

TIME() fucntion to return time from a DATETIME value:

SELECT TIME('2019-10-07 01:20:30');

The result is “01:20:30”

Similar to DATE() and TIME() functions, MySQL has YEAR(), QUARTER(), MONTH(), WEEK(), DAY(), HOUR(), MINUTE() and SECOND() functions to returns corresponding values from a DATETIME value:

SELECT 
    HOUR('2019-10-07 01:20:30') AS h,
    MINUTE('2019-10-07 01:20:30') AS min,
    SECOND('2019-10-07 01:20:30') AS s,
    DAY('2019-10-07 01:20:30') AS d,
    WEEK('2019-10-07 01:20:30') AS w,
    MONTH('2019-10-07 01:20:30') AS mo,
    QUARTER('2019-10-07 01:20:30') AS qrt,
    YEAR('2019-10-07 01:20:30') as y;

The result is:

h	min	s	d	w	mo	qrt	y
1	20	30	7	40	10	4	2019

DATE_FORMAT() function to format a DATETIME value to your preferred display format:

SELECT DATE_FORMAT('2019-10-07 01:20:30', '%H:%i:%s - %W %M %Y');

The result is: “01:20:30 - Monday October 2019”

DATEDIFF() function to return the difference in days between two DATETIME values:

SELECT DATEDIFF('2019-10-07 01:20:30', '2017-10-07 01:20:30');

The result is 730.

DATE_ADD() function to add a number of days, weeks, months, years,… to a DATETIME value:

SELECT 
    DATE_ADD('2019-10-04 01:20:30', INTERVAL 10 DAY) AS '10 days later',
    DATE_ADD('2019-10-04 01:20:30', INTERVAL 10 WEEK) AS '10 weeks later',
    DATE_ADD('2019-10-04 01:20:30', INTERVAL 10 MONTH) AS '10 months later',
    DATE_ADD('2019-10-04 01:20:30', INTERVAL 10 YEAR) AS '10 years later';

The result is

10 days later	10 weeks later	10 months later	10 years later
2019-10-14 01:20:30	2019-12-13 01:20:30	2020-08-04 01:20:30	2029-10-04 01:20:30

In reverse to DATE_ADD(), DATE_SUB() function is to subtract a number of days, weeks, months, years,… to a DATETIME value:

SELECT 
    DATE_SUB('2019-10-04 01:20:30', INTERVAL 10 DAY) AS '10 days earlier',
    DATE_SUB('2019-10-04 01:20:30', INTERVAL 10 WEEK) AS '10 weeks earlier',
    DATE_SUB('2019-10-04 01:20:30', INTERVAL 10 MONTH) AS '10 months earlier',
    DATE_SUB('2019-10-04 01:20:30', INTERVAL 10 YEAR) AS '10 years earlier';

The result is:

10 days earlier	10 weeks earlier	10 months earlier	10 years earlier
2019-09-24 01:20:30	2019-07-26 01:20:30	2018-12-04 01:20:30	2009-10-04 01:20:30

EXTRACT() function to extract parts from the DATETIME value:

SELECT
	EXTRACT(YEAR FROM '2019-10-07 01:20:30') AS y, 
	EXTRACT(MONTH FROM '2019-10-07 01:20:30') AS m, 
	EXTRACT(DAY FROM '2019-10-07 01:20:30') AS d,
	EXTRACT(YEAR_MONTH FROM '2019-10-07 01:20:30') AS ym;

The result is:

y	m	d	ym
2019	10	7	201910

DAYOFYEAR(), DAYOFMONTH(), DAYOFWEEK() functions to return the day of the year, month, or week for a DATETIME value, given the week starts with Sunday (Sunday = 1):

SELECT
	DAYOFYEAR('2019-10-07 01:20:30') AS doy,
	DAYOFMONTH('2019-10-07 01:20:30') AS dom,
	DAYOFWEEK('2019-10-07 01:20:30') AS dow;

The result is:

doy	dom	dow
280	7	2

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.