Section A.2. Date and Time Data Types

A.2. Date and Time Data Types

There are a few column data types for storing date and time
values. They are listed in Table A-1. The
table also lists the valid ranges for each data type. If a value that is
not permitted or that is outside the acceptable range for the data type is
inserted, zeros are used instead. You can override this feature by
starting the server with --sql-mode='ALLOW_INVALID_DATES. As of
version 5.0.2 of MySQL, warnings are generated when inserting invalid
dates or times. For dates that are inserted with only 2 digits for the
year, values from 00 to 69 are assumed to be in the 21st century. For
years from 70 to 99, they are assumed to be in the 20th century.

Table A-1. Date and time data types

Data type	Format	Range
DATE	yyyy-mm-dd	1000-01-01 to 9999-12-31
DATETIME	yyyy-mm-dd hh:mm:ss	1000-01-01 00:00:00 to 9999-12-31 00:00:00
TIMESTAMP	yyyy-mm-dd hh:mm:ss	1970-01-01 00:00:00 to 2037-12-31 23:59:59
TIME	hh:mm:ss	–838:59:59 to 838:59:59
YEAR[(2|4)]	yy or yyyy	1970 to 2069, or 1901 to 2155

Times values may be given either as a string or numerically. To
give them as a string, you can enter a value as d
hh:mm:ss.f. In this format, d stands for the
number of days, and has an allowable range of 0 to 34. The
f stands for a fractional number of seconds. This value
will not be stored, though. The ability to store fractional seconds is
expected to be added in future releases of MySQL. You don't have to
specify values for all elements of a time. Instead, you can enter a time
value using one of these formats: hh:mm:ss.f,
hh:mm:ss, hh:mm, or just
ss. If you want to include the number of days, you can
use these formats: d hh:mm:ss, d
hh:mm, or d hh. You can also drop the colons
and just enter hhmmss, but you can't add minutes onto
the end of that format. The data type TIMESTAMP stores the date and
time as the number of seconds since the epoch (its earliest date allowed),
but it displays this number with the format yyyy-mm-dd
hh:mm:ss. MySQL will automatically convert a date or time to its
numeric date equivalent when it is used in a numeric context, and it will
convert a numeric date to a date or time. If it isn't given a date, MySQL
will use a default of the current date—this is the primary difference
between this column data type and DATETIME.