Table Column Types for Date and Time Values

This section 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) 2015, 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;

Run DateTimeColumns.sql on MySQL 5.7 server, you will get an error, because of the timezone information in the time value.

C:\herong>set "MySQL=\Program Files\MySQL\MySQL Server 5.7"
C:\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'

Run DateTimeColumns.sql on MySQL 5.7 server, you will get the same error.

But run DateTimeColumns.sql on MySQL 5.0 server, you will get no error:

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

Run ExactNumberColumns.sql on MySQL 4.0 servers, you will get:

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:

Last update: 2015.

Table of Contents

 About This Book

 Introduction of SQL

 MySQL Introduction and Installation

 Introduction of MySQL Programs

 Perl Programs and MySQL Servers

 PHP Programs and MySQL Servers

 Java Programs and MySQL Servers

 Datatypes and Data Literals

 Operations and Expressions

 Character Strings and Bit Strings

 Commonly Used Functions

Table Column Types for Different Types of Values

 Table Column Types for Character Strings

 Table Column Types for Bit Strings

 Table Column Types for Exact Numbers

 Table Column Types for Approximate Numbers

Table Column Types for Date and Time Values

 Using DDL to Create Tables and Indexes

 Using DML to Insert, Update and Delete Records

 Using SELECT to Query Database

 Transaction Management and Isolation Levels

 Locks Used in MySQL

 Defining and Calling Stored Procedures

 Variables, Loops and Cursors Used in Stored Procedures

 Outdated Tutorials

 References

 PDF Printing Version