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
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 | |
yy |
1970 |
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 a time value using one of
these formats: hh:mm:ss.f
, or 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 datatype
TIMESTAMP
stores the date and time in a format of
yyyymmddhhmmss
, but displays it with the format of
yyyy-mm-dd hh:mm:ss
. MySQL will automatically
convert a date or time to its numeric equivalent when it is used in a
numeric context, and it will do the reverse as well.
3.145.184.113