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.
Data type | Format | Range |
|
| 1000-01-01 to 9999-12-31 |
|
| 1000-01-01 00:00:00 to 9999-12-31 00:00:00 |
|
| 1970-01-01 00:00:00 to 2037-12-31 23:59:59 |
|
| –838:59:59 to 838:59:59 |
|
| 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 ...
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.