{"id":4050,"date":"2015-11-04T23:52:34","date_gmt":"2015-11-05T07:52:34","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=4050"},"modified":"2023-12-27T17:54:24","modified_gmt":"2023-12-28T00:54:24","slug":"mysql-datetime","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-datetime\/","title":{"rendered":"MySQL DATETIME Data Type"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about MySQL DATETIME<\/code> data type and how to use some handy functions for manipulating DATETIME<\/code> effectively.<\/p>\n\n\n\n

Introduction to MySQL DATETIME data type<\/h2>\n\n\n\n

MySQL DATETIME<\/code> data type allows you to store a value that contains both date<\/a> and time<\/a>. <\/p>\n\n\n\n

When you\u00a0query data<\/a> from a DATETIME<\/code> column, MySQL displays the DATETIME<\/code> value in the following format:<\/p>\n\n\n

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