The DATETIME
,
DATE
, and
TIMESTAMP
types are related. This
section describes their characteristics, how they are similar,
and how they differ.
The DATETIME
type is used when
you need values that contain both date and time information.
MySQL retrieves and displays
DATETIME
values in
'YYYY-MM-DD HH:MM:SS'
format. The supported
range is '1000-01-01 00:00:00'
to
'9999-12-31 23:59:59'
.
The DATE
type is used when you
need only a date value, without a time part. MySQL retrieves and
displays DATE
values in
'YYYY-MM-DD'
format. The supported range is
'1000-01-01'
to
'9999-12-31'
.
For the DATETIME
and
DATE
range descriptions,
“supported” means that although earlier values
might work, there is no guarantee.
The TIMESTAMP
data type has a
range of '1970-01-01 00:00:01'
UTC to
'2038-01-19 03:14:07'
UTC. It has varying
properties, depending on the MySQL version and the SQL mode the
server is running in. These properties are described later in
this section.
You can specify DATETIME
,
DATE
, and
TIMESTAMP
values using any of a
common set of formats:
As a string in either 'YYYY-MM-DD
HH:MM:SS'
or 'YY-MM-DD
HH:MM:SS'
format. A “relaxed” syntax
is allowed: Any punctuation character may be used as the
delimiter between date parts or time parts. For example,
'98-12-31 11:30:45'
, '98.12.31
11+30+45'
, '98/12/31 11*30*45'
,
and '98@12@31 11^30^45'
are equivalent.
As a string in either 'YYYY-MM-DD'
or
'YY-MM-DD'
format. A
“relaxed” syntax is allowed here, too. For
example, '98-12-31'
,
'98.12.31'
,
'98/12/31'
, and
'98@12@31'
are equivalent.
As a string with no delimiters in either
'YYYYMMDDHHMMSS'
or
'YYMMDDHHMMSS'
format, provided that the
string makes sense as a date. For example,
'20070523091528'
and
'070523091528'
are interpreted as
'2007-05-23 09:15:28'
, but
'071122129015'
is illegal (it has a
nonsensical minute part) and becomes '0000-00-00
00:00:00'
.
As a string with no delimiters in either
'YYYYMMDD'
or 'YYMMDD'
format, provided that the string makes sense as a date. For
example, '20070523'
and
'070523'
are interpreted as
'2007-05-23'
, but
'071332'
is illegal (it has nonsensical
month and day parts) and becomes
'0000-00-00'
.
As a number in either YYYYMMDDHHMMSS
or
YYMMDDHHMMSS
format, provided that the
number makes sense as a date. For example,
19830905132800
and
830905132800
are interpreted as
'1983-09-05 13:28:00'
.
As a number in either YYYYMMDD
or
YYMMDD
format, provided that the number
makes sense as a date. For example,
19830905
and 830905
are interpreted as '1983-09-05'
.
As the result of a function that returns a value that is
acceptable in a DATETIME
,
DATE
, or
TIMESTAMP
context, such as
NOW()
or
CURRENT_DATE
.
A microseconds part is allowable in temporal values in some
contexts, such as in literal values, and in the arguments to or
return values from some temporal functions. Microseconds are
specified as a trailing .uuuuuu
part in the
value. Example:
mysql> SELECT MICROSECOND('2010-12-10 14:12:09.019473');
+-------------------------------------------+
| MICROSECOND('2010-12-10 14:12:09.019473') |
+-------------------------------------------+
| 19473 |
+-------------------------------------------+
However, microseconds cannot be stored into a column of any temporal data type. Any microseconds part is discarded.
Conversion of TIME
or
DATETIME
values to numeric form
(for example, by adding +0
) results in a
double value with a microseconds part of
.000000
:
mysql>SELECT CURTIME(), CURTIME()+0;
+-----------+---------------+ | CURTIME() | CURTIME()+0 | +-----------+---------------+ | 10:41:36 | 104136.000000 | +-----------+---------------+ mysql>SELECT NOW(), NOW()+0;
+---------------------+-----------------------+ | NOW() | NOW()+0 | +---------------------+-----------------------+ | 2007-11-30 10:41:47 | 20071130104147.000000 | +---------------------+-----------------------+
Illegal DATETIME
,
DATE
, or
TIMESTAMP
values are converted to
the “zero” value of the appropriate type
('0000-00-00 00:00:00'
or
'0000-00-00'
).
For values specified as strings that include date part
delimiters, it is not necessary to specify two digits for month
or day values that are less than 10
.
'1979-6-9'
is the same as
'1979-06-09'
. Similarly, for values specified
as strings that include time part delimiters, it is not
necessary to specify two digits for hour, minute, or second
values that are less than 10
.
'1979-10-30 1:2:3'
is the same as
'1979-10-30 01:02:03'
.
Values specified as numbers should be 6, 8, 12, or 14 digits
long. If a number is 8 or 14 digits long, it is assumed to be in
YYYYMMDD
or YYYYMMDDHHMMSS
format and that the year is given by the first 4 digits. If the
number is 6 or 12 digits long, it is assumed to be in
YYMMDD
or YYMMDDHHMMSS
format and that the year is given by the first 2 digits. Numbers
that are not one of these lengths are interpreted as though
padded with leading zeros to the closest length.
Values specified as nondelimited strings are interpreted using
their length as given. If the string is 8 or 14 characters long,
the year is assumed to be given by the first 4 characters.
Otherwise, the year is assumed to be given by the first 2
characters. The string is interpreted from left to right to find
year, month, day, hour, minute, and second values, for as many
parts as are present in the string. This means you should not
use strings that have fewer than 6 characters. For example, if
you specify '9903'
, thinking that represents
March, 1999, MySQL inserts a “zero” date value into
your table. This occurs because the year and month values are
99
and 03
, but the day
part is completely missing, so the value is not a legal date.
However, you can explicitly specify a value of zero to represent
missing month or day parts. For example, you can use
'990300'
to insert the value
'1999-03-00'
.
You can to some extent assign values of one date type to an object of a different date type. However, there may be some alteration of the value or loss of information:
If you assign a DATE
value to
a DATETIME
or
TIMESTAMP
object, the time
part of the resulting value is set to
'00:00:00'
because the
DATE
value contains no time
information.
If you assign a DATETIME
or
TIMESTAMP
value to a
DATE
object, the time part of
the resulting value is deleted because the
DATE
type stores no time
information.
Remember that although
DATETIME
,
DATE
, and
TIMESTAMP
values all can be
specified using the same set of formats, the types do not
all have the same range of values. For example,
TIMESTAMP
values cannot be
earlier than 1970
UTC or later than
'2038-01-19 03:14:07'
UTC. This means
that a date such as '1968-01-01'
, while
legal as a DATETIME
or
DATE
value, is not valid as a
TIMESTAMP
value and is
converted to 0
.
Be aware of certain problems when specifying date values:
The relaxed format allowed for values specified as strings
can be deceiving. For example, a value such as
'10:11:12'
might look like a time value
because of the “:
”
delimiter, but if used in a date context is interpreted as
the year '2010-11-12'
. The value
'10:45:15'
is converted to
'0000-00-00'
because
'45'
is not a legal month.
The server requires that month and day values be legal, and
not merely in the range 1 to 12 and 1 to 31, respectively.
With strict mode disabled, invalid dates such as
'2004-04-31'
are converted to
'0000-00-00'
and a warning is generated.
With strict mode enabled, invalid dates generate an error.
To allow such dates, enable
ALLOW_INVALID_DATES
. See
Section 5.1.8, “Server SQL Modes”, for more information.
MySQL does not accept timestamp values that include a zero
in the day or month column or values that are not a valid
date. The sole exception to this rule is the special value
'0000-00-00 00:00:00'
.
Dates containing two-digit year values are ambiguous because the century is unknown. MySQL interprets two-digit year values using the following rules:
Year values in the range 00-69
are
converted to 2000-2069
.
Year values in the range 70-99
are
converted to 1970-1999
.
User Comments
You can automatically convert an invalid date to the valid equivalent like this:
SELECT '2008-02-31' + INTERVAL 0 DAY;
# 2008-03-02
This lets you avoid storing invalid dates without bothering the user and without having to manually program a fix into your app. I suppose in >=5.0.2, you'd have to enable ALLOW_INVALID_DATES to avoid having it converted to '0000-00-00'. This also works with DATETIMEs.
Is it just me, or does this sentence:
"For example, if you specify '9903', thinking that represents March, 1999, MySQL inserts a “zero” date value into your table. This occurs because the year and month values are 99 and 03, but the day part is completely missing, so the value is not a legal date. However, you can explicitly specify a value of zero to represent missing month or day parts. For example, you can use '990300' to insert the value '1999-03-00'."
Conflict with this sentence?:
"MySQL does not accept timestamp values that include a zero in the day or month column or values that are not a valid date. The sole exception to this rule is the special value '0000-00-00 00:00:00'."
Not that I need to do that, but I thought I would point it out in case it needs a correction.
In reply to Greg Thorne on April 9 2009 5:04pm
This is no conflict: The first sentence regards date values, the second sentence is a special rule for timestamp values.
In addition to Linus Rachlis,
if you add 0 month to an invalid date, you could get last day of the month, for example
SELECT '2008-02-31' + INTERVAL 0 MONTH;
# 2008-02-29
It works as
SELECT LAST_DAY('2008-02-31');
# 2008-02-29
Add your own comment.