Table Column Types for Date and Time Values

MySQL Tutorials - Herong's Tutorial Examples

∟Table Column Types for Different Types of Values

∟Table Column Types for Date and Time Values

Describes 6 types of table columns to store date and time values: TIME, DATE, TIMESTAMP, TIME WITH TIME ZONE, TIMESTAMP WITH TIME ZONE, and INTERVAL.

There are 6 types of table columns to store date and time values: TIME, DATE, TIMESTAMP, TIME WITH TIME ZONE, TIMESTAMP WITH TIME ZONE, and INTERVAL.

"TIME" - Compound value of hour, minute, second, and fractional seconds.

"DATE" - Compound value of year, month, and day.

"TIMESTAMP" - Compound value of year, month, day, hour, minute, second and fractional seconds.

"TIME WITH TIME ZONE" - Compound value of hour, minute, second, fractional seconds and time zone.

"TIMESTAMP WITH TIME ZONE" - Compound value of year, month, day, hour, minute, second, fractional seconds and time zone.

"INTERVAL" - Value representing two different date time values.

Examples of date and time column types, DateTimeColumns.sql:

-- DateTimeColumns.sql
-- Copyright (c) 1999 HerongYang.com. All Rights Reserved.

CREATE TABLE DateTime (
   Line VARCHAR(2),
   A DATE,
   B TIME, -- MySQL does not support TIME(3)
   C TIMESTAMP,
   D TIME -- MySQL does not support TIME WITH TIME ZONE
--   E TIMESTAMP WITH TIME ZONE
--   F INTERVAL
   );
INSERT INTO DateTime VALUES (
   '1',
   DATE '1999-07-01',
   TIME '10:20:30.001',
   TIMESTAMP '1999-06-24 01:02:03.001',
   TIME '10:20:30.001-11:11'
   );
INSERT INTO DateTime VALUES (
   '2',
   '1999-07-02',
   '10:20:31.002',
   '1999-06-25 01:02:13.002',
   '10:20:31.002-12:12'
   );
INSERT INTO DateTime VALUES (
   '3',
   '10:20:33.002',
   '1999-07-03',
   '1999-06-26 01:02:13.003',
   '10:20:33.003-13:13'
   );
SELECT * FROM DateTime;
DROP TABLE DateTime;

Running DateTimeColumns.sql on MySQL 8.0, 5.7, and 5.6 servers gives you an error, because of the timezone information in the time value.

herong> %mysql%\bin\mysql --user=root --password=TopSecret test \
   < DateTimeColumns.sql

ERROR 1525 (HY000) at line 13: Incorrect TIME value: '10:20:30.001-11:11'

But running DateTimeColumns.sql on MySQL 5.0 server gives you no errors:

Line    A       B       C       D
1       1999-07-01      10:20:30        1999-06-24 01:02:03     10:20:30
2       1999-07-02      10:20:31        1999-06-25 01:02:13     10:20:31
3       0000-00-00      00:00:00        1999-06-26 01:02:13     10:20:33

Running DateTimeColumns.sql on MySQL 4.0 server gives you:

Line    A       B       C       D
1       1999-07-01      10:20:30        19990624010203  10:20:30
2       1999-07-02      10:20:31        19990625010213  10:20:31
3       0000-00-00      00:00:00        19990626010213  10:20:33

Note that:

Second fraction can be assigned to a column of TIME and TIMESTAMP.
Time zone can be assigned to a column of TIME and TIMESTAMP.
Line 3 tells us that a time literal can be assigned to a column of DATE. But the time value will be truncated and a '0' will be assigned to the column.
Line 3 also tells us that a date literal can be assigned to a column of TIME. But the date value will be truncated and a '0' will be assigned to the column.