The reference manual for mysql5.0 describes in detail the specifications and usage of datetime, date, and timestamp.
TheDATETIME
,DATE
, AndTIMESTAMP
Types are related. This section describes their characteristics, how they are similar, and how they differ.
TheDATETIME
Type is used when you need values that contain both date and time information. MySQL retrieves and displaysDATETIME
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'
.
TheDATE
Type is used when you need only a date value, without a time part. MySQL retrieves and displaysDATE
Values in'YYYY-MM-DD'
Format. The supported range is'1000-01-01'
To'9999-12-31'
.
ForDATETIME
AndDATE
Range descriptions, "supported" means that although earlier values might work, there is no guarantee.
TheTIMESTAMP
Data Type 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 specifyDATETIME
,DATE
, AndTIMESTAMP
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,'19970523091528'
And'970523091528'
Are interpreted'1997-05-23 09:15:28'
,'971122129015'
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,'19970523'
And'970523'
Are interpreted'1997-05-23'
,'971332'
Is illegal (it has nonsensical month and day parts) and becomes'0000-00-00'
.
As a number in eitherYYYYMMDDHHMMSS
OrYYMMDDHHMMSS
Format, provided that the number makes sense as a date. For example,19830905132800
And830905132800
Are interpreted'1983-09-05 13:28:00'
.
As a number in eitherYYYYMMDD
OrYYMMDD
Format, provided that the number makes sense as a date. For example,19830905
And830905
Are interpreted'1983-09-05'
.
As the result of a function that returns a value that is acceptable inDATETIME
,DATE
, OrTIMESTAMP
NOW()
OrCURRENT_DATE
. Context, such
IllegalDATETIME
,DATE
, OrTIMESTAMP
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 less10
.'1979-6-9'
Is the same'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 less10
.'1979-10-30 1:2:3'
Is the same'1979-10-30 01:02:03'
.
Values specified as numbers shoshould be 6, 8, 12, or 14 digits long. If a number is 8 or 14 digits long, it is assumed to be inYYYYMMDD
OrYYYYMMDDHHMMSS
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 inYYMMDD
OrYYMMDDHHMMSS
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 zeroes to the closest length.
Values specified as non-delimited 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 your parts as are present in the string. this means you shoshould 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 are99
And03
, But the day part is completely missing, so the value is not a legal date. however, you can explain icitly 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 assignDATE
Value toDATETIME
OrTIMESTAMP
Object, the time part of the resulting value is set'00:00:00'
BecauseDATE
Value contains no time information.
If you assignDATETIME
OrTIMESTAMP
Value toDATE
Object, the time part of the resulting value is deleted becauseDATE
Type stores no time information.
Remember that althoughDATETIME
,DATE
, AndTIMESTAMP
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 earlier1970
Or later2037
. This means that a date such'1968-01-01'
, While legal asDATETIME
OrDATE
Value, is not valid asTIMESTAMP
Value and is converted0
.
Be aware of certain pitfalls when specifying date values:
The relaxed format allowed for values specified as strings can be deceiving. For example, a value such'10:11:12'
Might look like a time value because of':
'Delimiter, but if used in a date context is interpreted as the year'2010-11-12'
. The value'10:45:15'
Is converted'0000-00-00'
'45'
Is not a legal month. Because
As of 5.0.2, 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'2004-04-31'
Are converted'0000-00-00'
And a warning is generated. With strict mode enabled, invalid dates generate an error. To allow such dates, enableALLOW_INVALID_DATES
. See section 5.2.5, "the server SQL mode", for more information.
Before MySQL 5.0.2, the MySQL server performs only basic checking on the validity of a date: the ranges for year, month, and day are 1000 to 9999, 00 to 12, and 00 to 31, respectively. any date containing parts not within these ranges is subject to conversion'0000-00-00'
. Please note that this still allows you to store invalid dates such'2002-04-31'
. To ensure that a date is valid, you should perform a check in your application.
Dates containing two-digit year values are ambiguous because the century is unknown. MySQL interprets two-digit year values using the following rules: