a guide to mysql_A2-04-05.MySQL DATA TYPES-A Complete Guide To MySQL DATETIME Data Type

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

You use MySQL DATETIME 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:

1

YYYY-MM-DDHH:MM:SS

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

A DATETIME value uses 5 bytes for storage. 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 PrecisionStorage (Bytes)

0

0

1, 2

1

3, 4

2

5, 6

3

For example, 2015-12-20 10:01:00.999999requires 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.

Note that before MySQL 5.6.4, DATETIME values requires 8 bytes storage instead of 5 bytes.

MySQL DATETIME vs. TIMESTAMP

MySQL provides another temporal data type that is similar to the DATETIME called

The TIMESTAMP requires 4 bytes while DATETIME requires 5 bytes. Both TIMESTAMP and DATETIMErequire 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.

1

SETtime_zone='+00:00';

Next, create a table named timestamp_n_datetime that consists of two columns: ts and dt with TIMESTAMP and DATETIME types using the following statement.

1

2

3

4

5

CREATETABLEtimestamp_n_datetime(

idINTAUTO_INCREMENTPRIMARY KEY,

tsTIMESTAMP,

dtDATETIME

);

Then, insert the current date and time into both ts and dt columns of the timestamp_n_datetime table,

1

2

INSERTINTOtimestamp_n_datetime(ts,dt)

VALUES(NOW(),NOW());

After that, query data from the timestamp_n_datetime table.

1

2

3

4

5

SELECT

ts,

dt

FROM

timestamp_n_datetime;

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.

1

2

3

4

5

6

7

SETtime_zone='+03:00';

SELECT

ts,

dt

FROM

timestamp_n_datetime;

As you can see, the value in the TIMESTAMP column is different. This is because the TIMESTAMP column stores the date and time value in UTC when we changed the time zone, the value of the TIMESTAMPcolumn 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 a 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

1

SET@dt=NOW();

To query the value of the @dt variable, you use the following SELECT statement:

1

SELECT@dt;

MySQL DATE function

To extract the date portion from a DATETIME value, you use the DATE function as follows:

1

SELECTDATE(@dt);

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.

1

2

3

4

5

6

7

CREATETABLEtest_dt(

idINTAUTO_INCREMENTPRIMARY KEY,

created_atDATETIME

);

INSERTINTOtest_dt(created_at)

VALUES('2015-11-05 14:29:36');

Suppose you want to know which row created on 2015-11-05, you use the following query:

1

2

3

4

5

6

SELECT

*

FROM

test_dt

WHERE

created_at='2015-11-05';

It returns no rows.

This is because the created_at column contains not only date but also time. To correct it, you use the DATE function as follows:

1

2

3

4

5

6

SELECT

*

FROM

test_dt

WHERE

DATE(created_at)='2015-11-05';

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:

1

SELECTTIME(@dt);

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 DATETIME value, you use the functions as shown in the following statement:

1

2

3

4

5

6

7

8

9

SELECT

HOUR(@dt),

MINUTE(@dt),

SECOND(@dt),

DAY(@dt),

WEEK(@dt),

MONTH(@dt),

QUARTER(@dt),

YEAR(@dt);

MySQL DATE_FORMAT function

To format a DATETIME value, you use the DATETIME value based on the %H:%i:%s - %W %M %Y format:

1

SELECTDATE_FORMAT(@dt,'%H:%i:%s - %W %M %Y');

MySQL DATE_ADD function

To add an intervalto a DATETIME value, you use DATE_ADD function as follows:

1

2

3

4

5

6

7

8

SELECT@dtstart,

DATE_ADD(@dt,INTERVAL1SECOND)'1 second later',

DATE_ADD(@dt,INTERVAL1MINUTE)'1 minute later',

DATE_ADD(@dt,INTERVAL1HOUR)'1 hour later',

DATE_ADD(@dt,INTERVAL1DAY)'1 day later',

DATE_ADD(@dt,INTERVAL1WEEK)'1 week later',

DATE_ADD(@dt,INTERVAL1MONTH)'1 month later',

DATE_ADD(@dt,INTERVAL1YEAR)'1 year later';

MySQL DATE_SUB function

To subtract an interval from a DATETIME value, you use DATE_SUB function as follows:

1

2

3

4

5

6

7

8

SELECT@dtstart,

DATE_SUB(@dt,INTERVAL1SECOND)'1 second before',

DATE_SUB(@dt,INTERVAL1MINUTE)'1 minute before',

DATE_SUB(@dt,INTERVAL1HOUR)'1 hour before',

DATE_SUB(@dt,INTERVAL1DAY)'1 day before',

DATE_SUB(@dt,INTERVAL1WEEK)'1 week before',

DATE_SUB(@dt,INTERVAL1MONTH)'1 month before',

DATE_SUB(@dt,INTERVAL1YEAR)'1 year before';

MySQL DATE_DIFF function

To calculate a difference in days between two DATETIME values, you use 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.

1

2

3

CREATETABLEdatediff_test(

dtDATETIME

);

Second, insert some rows into the datediff_test table.

1

2

3

4

5

6

7

8

INSERTINTOdatediff_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');

Third, use the DATEDIFF function to compare the current date and time with the value in each row of the datediff_test table.

1

2

3

4

5

SELECT

dt,

DATEDIFF(NOW(),dt)

FROM

datediff_test;

In this tutorial, you have learned about MySQL DATETIME data type and some useful DATETIME functions.