Home / Database Structure / Data Types / Question No: 113

I need to know detail about DATE, TIME, DATETIME, and YEAR Data Types in MySQL 5. Any detail please?

The DATE data type represents date values in 'YYYY-MM-DD' format. The supported range of DATE values is '1000-01-01' to '9999-12-31'. You might be able to use earlier dates than that, but it's better to stay within the supported range to avoid unexpected behavior.

The TIME data type represents time values in 'hh:mm:ss' format. The range of TIME columns is '-838:59:59' to '838:59:59'. This is outside the time-of-day range of '00:00:00' to '23:59:59' because TIME columns can be used to represent elapsed time. Thus, values might be larger than time-of-day values, or even negative.

The DATETIME data type stores date-and-time values in 'YYYY-MM-DD hh:mm:ss' format. It's similar to a combination of DATE and TIME values, but the TIME part represents time of day rather than elapsed time and has a range limited to '00:00:00' to '23:59:59'. The date part of DATETIME columns has the same range as DATE columns; combined with the TIME part, this results in a DATETIME range from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

The YEAR data type represents year-only values. You can declare such columns as YEAR(4) or YEAR(2) to obtain a four-digit or two-digit display format. If you don't specify any display width, the default is four digits.

If you don't need a full date and the range of values you need to store falls into the YEAR range, consider using YEAR to store temporal values. It's a very space-efficient data type because values require only one byte of storage each.

The following table summarizes the storage requirements and ranges for the date and time data types.

Type	Storage Required	Range
DATE	3 bytes	'1000-01-01' to '9999-12-31'
TIME	3 bytes	'-838:59:59' to '838:59:59'
DATETIME	8 bytes	'1000-01-01 00:00:00' to '9999-12-31 23:59:59'
YEAR	1 byte	1901 to 2155 (for YEAR(4)), 1970 to 2069 (for YEAR(2))