DATETIME, DATE, and TIMESTAMP Types
DATETIME, DATE, and TIMESTAMP Types 
2006-10-29 23:05:05
大ä¸å°
DATETIME type used at the same time you need to include the date and time when the value of information. MySQL and retrieval to 'YYYY-MM-DD HH: MM: SS' format DATETIME value, the scope of the support of'1000-01-01 00:00:00 'to'9999-12-31 23:59:59' . ( "Support" means that despite the earlier values may work, but can not guarantee that they can.)
DATE type you only need to use the value date, no part of the time. MySQL and retrieval to 'YYYY-MM-DD' format DATE value, the scope of the support of'1000-01-01 'to'9999-12-31'.
TIMESTAMP out type of a type, you can use it to automatically use the current date and time markers INSERT or UPDATE operation. If you have more than TIMESTAMP, and only the first automatic updates.
Automatic updating of a TIMESTAMP out in any of the following conditions occur:
Not explicitly listed in a INSERT or LOAD DATA INFILE specified in the statement.
Not explicitly listed in a designated UPDATE statement and some other changes in value. (UPDATE attention to the establishment of a value as it has some, this will not lead to TIMESTAMP are being updated, because if you set it as a current value of MySQL changes to the neglect of efficiency.)
You clearly set TIMESTAMP as NULL.
In addition to a series outside the TIMESTAMP also can be set to the current date and time, as long as will be set out NULL, or NOW ().
Through a clear set of values that you can set any TIMESTAMP as different from the current date and time values, even for a TIMESTAMP out as well. For example, if, when you create a trip, you want a TIMESTAMP is set to the current date and time, but in the future will be updated at any time, do not change, you can use this attribute:
MySQL expert to be created when setting out, which will initialize it for the current date and time.
When you visit the subsequent implementation of the rest of the changes are clearly set TIMESTAMP as its current value.
On the other hand, you may find that, when the line was created and from the subsequent changes, it is easy to use one you use NOW () initialization of DATETIME out.
TIMESTAMP value can be of a certain time, from 1970 until the beginning of 2037, the accuracy of one second, and its value as a digital display.
MySQL retrieval and display in the TIMESTAMP value depends on the size of the display format the following table. "Complete" TIMESTAMP format is 14, but can be used more TIMESTAMP out short to create the display size:
Types display format
TIMESTAMP (14) YYYYMMDDHHMMSS
TIMESTAMP (12) YYMMDDHHMMSS
TIMESTAMP (10) YYMMDDHHMM
TIMESTAMP (8) YYYYMMDD
TIMESTAMP (6) YYMMDD
TIMESTAMP (4) YYMM
TIMESTAMP (2) YY
TIMESTAMP out all have the same storage size, size does not consider the show. The most common size of the display is 6,8,12, and 14. You can specify a time table to create the display of arbitrary size, but the value 0 or than the 14 to 14 were mandatory. In the range from 1 to 13 odd numerical size was mandatory for the next one even more.
Use a common format in any one set, you can specify DATETIME, DATE, and TIMESTAMP value:
'YYYY-MM-DD HH: MM: SS' or 'YY-MM-DD HH: MM: SS' format of a string. Allow a "liberal" grammar - punctuation can be used in any part of the date and time of the separator. For example, the'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 synonymous.
'YYYY-MM-DD' or 'YY-MM-DD' format of a string. Allow a "liberal" syntax. For example, the'98-12-31 ','98 .12.31','98 / 12/31 'and'98 @ 12 @ 31' are synonymous.
'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS' format there is no separation of a string, for example,'19970523091528 'and'970523091528' be interpreted as'1997-05-23 09:15:28 ', but'971122459015' is not legal (It is pointless minutes) and a'0000-00-00 00:00:00 '.
'YYYYMMDD' or 'YYMMDD' format there is no separation of a string, if that is a date string. For example,'19970523 'and'970523' be interpreted as'1997-05-23 ','971332' is not legitimate (it is meaningless, and some days) and a'0000-00-00 '.
YYMMDDHHMMSS YYYYMMDDHHMMSS or a digital format, if that figure is a date. For example, 19830905132800 and 830905132800 be interpreted as'1983-09-05 13:28:00 '.
YYYYMMDD YYMMDD format or a figure, if that figure is a date. For example, 19830905 and 830905 be interpreted as'1983-09-05 '.
A return value can be a DATETIME, DATE, or TIMESTAMP acceptable in the context of environmental functions, such as NOW () or CURRENT_DATE.
Unlawful DATETIME, DATE, or TIMESTAMP value is transformed into the appropriate type of "zero" value ('0000-00-00 00:00:00 ','0000-00-00' or 00 trillion).
Including the date of separation at designated to a string value, unnecessary for less than 10 days, or the value of the designated two figures,'1979-6-9 'and'1979-06-09' is the same . Similarly, for some time, including the separation of designated to a string value, not for less than 10 hours, or seconds on the designated two figures,'1979-10-30 1:2:3 'and'1979-10 -30 01:02:03 'is the same.
Figures should be designated as 6,8,12 or 14 long. If the figure is 8 or 14 long, it is presumed to YYYYMMDD format or YYYYMMDDHHMMSS pretext four years and figures are given. If the figures are 6 or 12 long, it was assumed that a YYMMDD format or YYMMDDHHMMSS pretext two years and figures are given. Length is not one of these figures by filling ahead to the nearest zero length to explain.
Designated as a separator string used to set the length of them to explain. If the string length is 8 or 14 characters, the first four years is presumed characters are, otherwise years is presumed pretext two characters are given. The string has a number of strings from left to right side be interpreted to identify the year, month, day, hour, minute and second value, which means that you should not use less than six characters of the characters string. For example, if you specify'9903 ', that will represent March 1999, you will find that MySQL to a "zero" dates inserted into your table, it is because the month and year of 99 and 03, but the date of loss ( zero), so the value is not a legitimate date.
TIMESTAMP out the use of the designated value of the complete accuracy of the value of legitimate storage, display size is not considered. This has several implications:
Always designated the year, month, and, even if you are the type TIMESTAMP (4) or TIMESTAMP (2). Otherwise, the value will not be a legitimate date and 0 will be stored.
If you use ALTER TABLE widen a narrow TIMESTAMP out, had previously been "hidden" information will be displayed.
Similarly, a narrow TIMESTAMP out will not lead to loss of information, in addition to perceived value in the show, the less information is shown.
Despite the TIMESTAMP values are stored as complete accuracy directly operated store of value function is the only UNIX_TIMESTAMP (), other formatting functions in the operation of the retrieval of values, which means that you can not use functions such as HOUR () or SECOND (), Unless the TIMESTAMP value of the relevant parts were included in the formatted value. For example, a TIMESTAMP are part of the Department of HH be displayed, unless the display size is at least 10, in a short period of use, try using a TIMESTAMP value HOUR () result in a meaningless result.
To a certain extent, you can type a date assign a different value to the date type of object. However, that may change some of the losses or information:
If you assign a value of a DATETIME DATE TIMESTAMP or object, part of the time value of the results is set for'00: 00:00 'because DATE does not include the time value of information.
If you will be a DATETIME TIMESTAMP or assign a value DATE object, the result of the deletion of the time, not because DATE type of information storage time.
Remember, although DATETIME, DATE, and TIMESTAMP values can all use the same format to specify sets, but not all types have the same value range. For example, the TIMESTAMP value can not earlier than 1970 or later than 2037 network, which means that a date such as'1968-01-01 ', or when, as a DATETIME value DATE legitimate, it is not a correct TIMESTAMP value, and if assigned to the Such an object, which will be transformed into 0.
When the specified date value, Beware of certain deficiencies:
Allow designated as a string value of the relaxed format can be deceiving. For example, values such as'10: 11:12 'may seem like the time value, because ":" separation, but if the use of a date in, the context will be interpreted as a year into'2010-11-12'. Duty'10: 45:15 'will be transformed into'0000-00-00' because'45 'is not a legitimate month.
The two figures in the designated value is ambiguous, because the century is unknown. MySQL use the following interpretation of the rules in two values:
In the 00-69 range of annual value was transformed into 2000-2069.
Fan Wai 70-99 in the annual value was transformed into 1970-1999.
Tags: Date, java date, Timestamp, Types