– introduce you to the DATETIME datatype and some useful functions to manipulate DATETIME values. (1)
Summary: in this tutorial, you will learn about MySQL DATETIME data type and how to use some handy functions for manipulating DATETIME effectively.
Introduction to MySQL DATETIME data type
MySQL DATETIME data type allows you to store a value that contains both date and time.
When you query data from a DATETIME column, MySQL displays the DATETIME value in the following format:
'YYYY-MM-DD HH:MM:SS'Code language: SQL (Structured Query Language) (sql)When you insert a value into a DATETIME column, you use the same format. For example:
INSERT INTO table_name(datetime_column)
VALUES('2023-12-31 15:30:45');Code language: JavaScript (javascript)To populate a column with the current date and time, you use the result of the CURRENT_TIMESTAMP or NOW() function as the default value. For example:
CREATE TABLE events(
id INT AUTO_INCREMENT PRIMARY KEY,
event_name VARCHAR(255) NOT NULL,
started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO events(event_name)
VALUES('Connected to MySQL Server');
SELECT * FROM events;Code language: PHP (php)Output:
+----+---------------------------+---------------------+
| id | event_name | started_at |
+----+---------------------------+---------------------+
| 1 | Connected to MySQL Server | 2023-12-28 07:51:18 |
+----+---------------------------+---------------------+
1 row in set (0.00 sec)Code language: JavaScript (javascript)By default, DATETIME values range from 1000-01-01 00:00:00 to 9999-12-31 23:59:59. MySQL uses 5 bytes to store a DATETIME value.
In addition, a DATETIME value can include a trailing fractional second up to microseconds with the format YYYY-MM-DD HH:MM:SS[.fraction] e.g., 2015-12-20 10:01:00.999999.
When including the fractional second precision, DATETIME values require more storage as illustrated in the following table:
Fractional Seconds Precision | Storage (Bytes) |
0 | 0 |
1, 2 | 1 |
3, 4 | 2 |
5, 6 | 3 |
For example, 2015-12-20 10:01:00.999999 requires 8 bytes, 5 bytes for 2015-12-20 10:01:00 and 3 bytes for .999999 while 2015-12-20 10:01:00.9 requires only 6 bytes, 1 byte for the fractional second precision.
MySQL DATETIME vs.TIMESTAMP
MySQL provides another temporal data type that is similar to the DATETIME called TIMESTAMP.
The TIMESTAMP requires 4 bytes while DATETIME requires 5 bytes. Both TIMESTAMPand DATETIME require additional bytes for fractional seconds precision.
TIMESTAMP values range from 1970-01-01 00:00:01 UTC to 2038-01-19 03:14:07 UTC. If you want to store temporal values that are beyond 2038, you should use DATETIME instead of TIMESTAMP.
MySQL stores TIMESTAMP in UTC value. However, MySQL stores the DATETIME value as is without timezone. Let’s see the following example.
First, set the timezone of the current connection to +00:00.
SET time_zone = '+00:00';Code language: SQL (Structured Query Language) (sql)Next, create a table named timestamp_n_datetime that consists of two columns: tsand dt with TIMESTAMP and DATETIME types using the following statement.
CREATE TABLE timestamp_n_datetime (
id INT AUTO_INCREMENT PRIMARY KEY,
ts TIMESTAMP,
dt DATETIME
);Code language: SQL (Structured Query Language) (sql)Then, insert the current date and time into both ts and dt columns of the timestamp_n_datetime table,
INSERT INTO timestamp_n_datetime(ts,dt)
VALUES(NOW(),NOW());Code language: SQL (Structured Query Language) (sql)After that, query data from the timestamp_n_datetime table.
SELECT
ts,
dt
FROM
timestamp_n_datetime;Code language: SQL (Structured Query Language) (sql)
Both values in DATETIME and TIMESTAMP columns are the same.
Finally, set the connection’s time zone to +03:00 and query data from the timestamp_n_datetime table again.
SET time_zone = '+03:00';
SELECT
ts,
dt
FROM
timestamp_n_datetime;Code language: SQL (Structured Query Language) (sql)
The output indicates that the value in the TIMESTAMP column is different. This is because the TIMESTAMP column stores the date and time value in UTC when we change the time zone, the value of the TIMESTAMP column is adjusted according to the new time zone.
It means that if you use the TIMESTAMP data to store date and time values, you should take serious consideration when you move your database to a server located in a different time zone.
MySQL DATETIME functions
The following statement sets the variable @dt to the current date and time using the NOW() function.
SET @dt = NOW();Code language: SQL (Structured Query Language) (sql)To query the value of the @dt variable, you use the following SELECT statement:
SELECT @dt;Code language: SQL (Structured Query Language) (sql)
MySQL DATE() function
To extract the date portion from a DATETIME value, you use the DATE function as follows:
SELECT DATE(@dt);Code language: SQL (Structured Query Language) (sql)
This function is very useful in case you want to query data based on a date but the data stored in the column is based on both date and time.
Let’s see the following example.
CREATE TABLE test_dt (
id INT AUTO_INCREMENT PRIMARY KEY,
created_at DATETIME
);
INSERT INTO test_dt(created_at)
VALUES('2015-11-05 14:29:36');Code language: SQL (Structured Query Language) (sql)Suppose you want to know which row created on 2015-11-05, you use the following query:
SELECT
*
FROM
test_dt
WHERE
created_at = '2015-11-05';Code language: SQL (Structured Query Language) (sql)It returns no rows.
This is because the created_at column contains not only the date but also the time. To correct it, you use the DATE function as follows:
SELECT
*
FROM
test_dt
WHERE
DATE(created_at) = '2015-11-05';Code language: SQL (Structured Query Language) (sql)
It returns one row as expected. In case the table has many rows, MySQL has to perform a full table scan to locate the rows that match the condition.
MySQL TIME function
To extract the time portion from a DATETIME value, you use the TIME function as the following statement:
SELECT TIME(@dt);Code language: SQL (Structured Query Language) (sql)
MySQL YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE and SECOND functions
To get the year, quarter, month, week, day, hour, minute, and second from a DATETIMEvalue, you use the functions as shown in the following statement:
SELECT
HOUR(@dt),
MINUTE(@dt),
SECOND(@dt),
DAY(@dt),
WEEK(@dt),
MONTH(@dt),
QUARTER(@dt),
YEAR(@dt);Code language: SQL (Structured Query Language) (sql)
MySQL DATE_FORMAT function
To format a DATETIME value, you use the DATE_FORMAT function. For example, the following statement formats a DATETIME value based on the %H:%i:%s - %W %M %Y format:
SELECT DATE_FORMAT(@dt, '%H:%i:%s - %W %M %Y');Code language: SQL (Structured Query Language) (sql)
MySQL DATE_ADD function
To add an interval to a DATETIME value, you use DATE_ADD function as follows:
SELECT @dt start,
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';Code language: SQL (Structured Query Language) (sql)
MySQL DATE_SUB function
To subtract an interval from a DATETIME value, you use DATE_SUB function as follows:
SELECT @dt start,
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';Code language: SQL (Structured Query Language) (sql)
MySQL DATE_DIFF function
To calculate a difference in days between two DATETIME values, you use the DATEDIFF function. Notice that the DATEDIFF function only considers the date part of a DATETIME value in the calculation.
See the following example.
First, create a table named datediff_test that has one column whose data type is DATETIME.
CREATE TABLE datediff_test (
dt DATETIME
);Code language: SQL (Structured Query Language) (sql)Second, insert some rows into the datediff_test table.
INSERT INTO datediff_test(dt)
VALUES('2010-04-30 07:27:39'),
('2010-05-17 22:52:21'),
('2010-05-18 01:19:10'),
('2010-05-22 14:17:16'),
('2010-05-26 03:26:56'),
('2010-06-10 04:44:38'),
('2010-06-13 13:55:53');Code language: SQL (Structured Query Language) (sql)Third, use the DATEDIFF function to compare the current date and time with the value in each row of the datediff_test table.
SELECT
dt,
DATEDIFF(NOW(), dt)
FROM
datediff_test;Code language: SQL (Structured Query Language) (sql)
In this tutorial, you have learned about MySQL DATETIME data type and some useful DATETIME functions.