Date Data Types (Managing and Using MySQL, 2nd Edition)

16.3. Date Data Types

MySQL date types are extremely flexible tools for storing date information. They are also extremely forgiving in the belief that it is up to the application, not the database, to validate date values. MySQL only checks that months range from 0 to 12 and dates range from 0 to 31. February 31, 2001, is therefore a legal MySQL date. More useful, however, is the fact that February 0, 2001, is a legal date. In other words, you can use 0 to signify dates in which you do not know a particular piece of the date.

Though MySQL is somewhat forgiving on the input format, you should actually attempt to format all date values in your applications in MySQL's native format to avoid any confusion. MySQL always expects the year to be the left-most element of a date format. If you assign an illegal value in an SQL operation, MySQL will insert a zero for that value.

MySQL will also perform automatic conversion of date and time values to integer values when used in an integer context.

DATE

Syntax

DATE

Format

YYYY-MM-DD (2001-01-01)

Storage

3 bytes

Description

Stores a date in the range of January 1, 1000 ('1000-01-01') to December 31, 9999 ('9999-12-31') in the Gregorian calendar.

DATETIME

Syntax

DATETIME

Format

YYYY-MM-DD hh:mm:ss (2001-01-01 01:00:00)

Storage

8 bytes

Description

Stores a specific time in the range of 12:00:00 AM, January 1, 1000 ('1000-01-01 00:00:00') to 11:59:59 P.M., December 31, 9999 ('9999-12-31 23:59:59') in the Gregorian calendar.

TIME

Syntax

TIME

Format

hh:mm:ss (06:00:00)

Storage

3 bytes

Description

Stores a time value in the range of midnight ('00:00:00') to one second before midnight ('23:59:59').

TIMESTAMP

Syntax

TIMESTAMP[(display_size)]

Format

YYYYMMDDhhmmss (20010101060000)

Storage

4 bytes

Description

A simple representation of a point in time down to the second in the range of midnight on January 1, 1970, to one minute before midnight on December 31, 2037. Its primary utility is keeping track of table modifications. When you insert a NULL value into a TIMESTAMP column, the current date and time are inserted instead. When you modify any value in a row with a TIMESTAMP column, the first TIMESTAMP column will be automatically updated with the current date and time.

YEAR

Syntax

YEAR

Format

YYYY (2001)

Storage

1 byte

Description

Stores a year of the Gregorian calendar in the range of 1900 to 2155.