Introduction to MySQL DATETIME
MySQL DATETIME stores date and time with up to microsecond precision without retaining a time zone; compare it with TIMESTAMP and learn offset-input behavior.
In MySQL, you use DATETIME to store values that contain dates and times. When you query data from a DATETIME column, the value of the MySQL DATETIME column is displayed in the following format:
YYYY-MM-DD HH:MM:SSWithout fractional seconds, DATETIME ranges from 1000-01-01 00:00:00 to 9999-12-31 23:59:59. With six fractional digits, the range is 1000-01-01 00:00:00.000000 through 9999-12-31 23:59:59.499999.
A DATETIME value is stored using 5 bytes. Additionally, a DATETIME value can include a trailing fractional second in the format: YYYY-MM-DD HH:MM:SS[.fraction]. For example, 2015-12-20 10:01:00.999999. When including fractional second precision, the DATETIME value requires more storage space, as shown in the following table:
| Fractional second precision | Storage (bytes) |
|---|---|
| 0 | 0 |
| 1, 2 | 1 |
| 3, 4 | 2 |
| 5, 6 | 3 |
For example, 2015-12-20 10:01:00.999999 needs 8 bytes to store, 5 bytes for 2015-12-20 10:01:00 and 3 bytes for .999999. While 2015-12-20 10:01:00.9 only needs 6 bytes, 1 byte is used for fractional seconds precision.
Note that prior to MySQL 5.6.4, DATETIME values needs 8 bytes for storage, not 5 bytes.
MySQL datetime and timestamp
MySQL provides another time data type similar to DATETIME, called TIMESTAMP.
TIMESTAMP requires 4 bytes, while DATETIME requires 5 bytes. Both TIMESTAMP and DATETIME require extra bytes for fractional seconds.
TIMESTAMP values range from 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC. If you want to store time values beyond the year 2038, you should use DATETIME instead TIMESTAMP.
MySQL TIMESTAMP stores values in UTC and converts them through the session time zone on retrieval. A DATETIME column does not store a time zone and is not converted on retrieval. MySQL 8.0.19 and later also accept offsets in DATETIME literals on insert: MySQL converts the input to the session time zone before storing the resulting wall-clock value, then discards the offset. For example:
SET time_zone = '+00:00';
CREATE TEMPORARY TABLE datetime_offset_example (
value DATETIME
);
INSERT INTO datetime_offset_example
VALUES ('2020-01-01 10:10:10+05:30');
SELECT value FROM datetime_offset_example;
value
2020-01-01 04:40:10The stored DATETIME contains no offset; after insertion it will not be adjusted when the session time zone changes. See MySQL’s date/time literal rules and 8.0.19 release notes.
The following example compares ordinary DATETIME and TIMESTAMP values as the session time zone changes.
First, set the current connection’s time zone to +00:00.
SET time_zone = '+00:00';
Next, use the following statement to create a table named timestamp_n_datetime, which consists of two columns: ts and dt are the TIMESTAMP and DATETIME data types respectively.
CREATE TABLE timestamp_n_datetime (
id INT AUTO_INCREMENT PRIMARY KEY,
ts TIMESTAMP,
dt DATETIME
);
Then, insert the current date and time into ts and dt columns of the timestamp_n_datetime table,
INSERT INTO timestamp_n_datetime(ts,dt)
VALUES(NOW(),NOW());
After that, query the data from the timestamp_n_datetime table .
SELECT ts, dt
FROM timestamp_n_datetime;
+---------------------+---------------------+
| ts | dt |
+---------------------+---------------------+
| 2022-03-28 02:52:15 | 2022-03-28 02:52:15 |
+---------------------+---------------------+the values in DATETIME and TIMESTAMP columns are the same.
Finally, set the connection’s timezone to +03:00 and query the data from the timestamp_n_datetime table again.
SET time_zone = '+03:00';
SELECT ts, dt
FROM timestamp_n_datetime;
+---------------------+---------------------+
| ts | dt |
+---------------------+---------------------+
| 2022-03-28 05:52:15 | 2022-03-28 02:52:15 |
+---------------------+---------------------+As you can see, the value in the columns TIMESTAMP has been changed. This is because the TIMESTAMP column stores the date and time values in UTC, and the value of the TIMESTAMP column is adjusted according to the new time zone.
This means that if you are using TIMESTAMP data to store date and time values, you should consider carefully when moving your database to a server in a different time zone.
MySQL datetime functions
The following statement uses the NOW() function to set @dt the variable to the current date and time.
SET @dt = NOW();
To query the value of a @dt variable , use the following SELECT statement :
SELECT @dt;
+---------------------+
| @dt |
+---------------------+
| 2022-03-28 05:56:27 |
+---------------------+MySQL DATE function
To extract the date part from a DATETIME value, use the DATE function as follows:
SELECT DATE(@dt);
+------------+
| DATE(@dt) |
+------------+
| 2022-03-28 |
+------------+MySQL TIME function
To extract the time part from a DATETIME value use the TIME function as the following statement:
SELECT TIME(@dt);
+-----------+
| TIME(@dt) |
+-----------+
| 05:56:27 |
+-----------+MySQL YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, and SECOND functions
To get the year, quarter, month, week, day, hour, minute, and second from the DATETIME value , use the YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE and SECOND functions as shown in the following statement:
SELECT
YEAR(@dt),
QUARTER(@dt),
MONTH(@dt),
WEEK(@dt),
DAY(@dt),
HOUR(@dt),
MINUTE(@dt),
SECOND(@dt);
+-----------+--------------+------------+-----------+----------+-----------+-------------+-------------+
| YEAR(@dt) | QUARTER(@dt) | MONTH(@dt) | WEEK(@dt) | DAY(@dt) | HOUR(@dt) | MINUTE(@dt) | SECOND(@dt) |
+-----------+--------------+------------+-----------+----------+-----------+-------------+-------------+
| 2022 | 1 | 3 | 13 | 28 | 5 | 56 | 27 |
+-----------+--------------+------------+-----------+----------+-----------+-------------+-------------+MySQL DATE_FORMAT function
To format a DATETIME value, use the DATE_FORMAT function. For example, the following statement formats DATETIME values according to the format %H:%i:%s - %W %M %Y:
SELECT DATE_FORMAT(@dt, '%H:%i:%s - %W %M %Y');
+-----------------------------------------+
| DATE_FORMAT(@dt, '%H:%i:%s - %W %M %Y') |
+-----------------------------------------+
| 05:56:27 - Monday March 2022 |
+-----------------------------------------+MySQL DATE_ADD function
To add an interval to a DATETIME value , use the DATE_ADD function as follows:
SELECT @dt `now`,
DATE_ADD(@dt, INTERVAL 1 SECOND) `1 second later`,
DATE_ADD(@dt, INTERVAL 1 MINUTE) `1 minute later`,
DATE_ADD(@dt, INTERVAL 1 HOUR) `1 hour later`,
DATE_ADD(@dt, INTERVAL 1 DAY) `1 day later`,
DATE_ADD(@dt, INTERVAL 1 WEEK) `1 week later`,
DATE_ADD(@dt, INTERVAL 1 MONTH) `1 month later`,
DATE_ADD(@dt, INTERVAL 1 YEAR) `1 year later`\G
*************************** 1\. row ***************************
now: 2022-03-28 05:56:27
1 second later: 2022-03-28 05:56:28
1 minute later: 2022-03-28 05:57:27
1 hour later: 2022-03-28 06:56:27
1 day later: 2022-03-29 05:56:27
1 week later: 2022-04-04 05:56:27
1 month later: 2022-04-28 05:56:27
1 year later: 2023-03-28 05:56:27MySQL DATE_SUB function
To subtract an interval from a DATETIME value , use the DATE_SUB function as follows:
SELECT @dt `now`,
DATE_SUB(@dt, INTERVAL 1 SECOND) `1 second before`,
DATE_SUB(@dt, INTERVAL 1 MINUTE) `1 minute before`,
DATE_SUB(@dt, INTERVAL 1 HOUR) `1 hour before`,
DATE_SUB(@dt, INTERVAL 1 DAY) `1 day before`,
DATE_SUB(@dt, INTERVAL 1 WEEK) `1 week before`,
DATE_SUB(@dt, INTERVAL 1 MONTH) `1 month before`,
DATE_SUB(@dt, INTERVAL 1 YEAR) `1 year before`\G
*************************** 1\. row ***************************
now: 2022-03-28 05:56:27
1 second before: 2022-03-28 05:56:26
1 minute before: 2022-03-28 05:55:27
1 hour before: 2022-03-28 04:56:27
1 day before: 2022-03-27 05:56:27
1 week before: 2022-03-21 05:56:27
1 month before: 2022-02-28 05:56:27
1 year before: 2021-03-28 05:56:27MySQL DATE_DIFF function
To calculate the difference in days between two DATETIME values , use the DATEDIFF function. Note that the DATEDIFF function only considers the date part of the DATETIME value.
SELECT DATEDIFF(@dt, '2022-01-01');
+-----------------------------+
| DATEDIFF(@dt, '2022-01-01') |
+-----------------------------+
| 86 |
+-----------------------------+Conclusion
In this tutorial, you learned about MySQL DATETIME data type and some useful datetime functions.
- A
DATETIMEvalue contains date and time, and can also contain fractional seconds. - The format of a
DATETIMEvalue isYYYY-MM-DD HH:MM:SS. - A
DATETIMEvalue does not contain time zone information.