MySQL in a Nutshell

Date and Time Datatypes

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

Table A-3. 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[( width )]	yyyymmddhhmmss	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 as either a string, or numerically. As a string, you may enter a value as d hh:mm:ss.f. In this format, d stands for the number of days, with an allowable range of 0 to 34. The f stands for a fractional number of seconds. This value will not be stored, though. Storing 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 ...