Introduction to MySQL DATETIME

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

By default, DATETIME values range from 1000-01-01 00:00:00 to 9999-12-31 23:59:59.

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. However, MySQL stores the DATETIME value without a time zone. Let’s look at the example below.

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

MySQL 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:27

MySQL 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 DATETIME value contains date and time, and can also contain fractional seconds.
The format of a DATETIME value is YYYY-MM-DD HH:MM:SS.
A DATETIME value does not contain time zone information.