MySQL in a Nutshell, 2nd Edition

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 ...