The difference between a date datetime timestamp in a transfer database
DATETIME, date, and timestamp types are related. This article describes their characteristics, how they are similar and different.
A datetime type is used when you need a value that contains both date and time information. MySQL retrieves and displays the datetime value in ' yyyy-mm-dd HH:MM:SS ' format, supported by ' 1000-01-01 00:00:00 ' to ' 9999-12-31 23:59:59 '. ("support" means that although earlier values may work, there is no guarantee that they can.) ）
The date type is used when you only need a date value, and there is no time section. MySQL retrieves and displays the date value in ' YYYY-MM-DD ' format, supported by ' 1000-01-01 ' to ' 9999-12-31 '.
The timestamp column type provides a type that you can use to automatically mark an INSERT or update operation with the current date and time. If you have more than one timestamp column, only the first one is automatically updated.
The automatic update of the first timestamp column occurs under any of the following conditions:
The column is not explicitly specified in an INSERT or load DATA infile statement.
The column is not explicitly specified in an UPDATE statement and some additional columns change values. (Note An update setting that lists the values it already has, which will not cause the timestamp column to be updated, because if you set a list as its current value, MySQL ignores the changes for efficiency.) ）
You explicitly set the timestamp column to be null.
The timestamp column other than the first one can also be set to the current date and time, as long as the column is set to NULL, or now ().
By explicitly setting the desired value, you can set any timestamp column as a value different from the current date and time, even for the first timestamp columns. For example, if, when you create a row, you want a timestamp to be set to the current date and time, but do not change at any later time when the line is updated, you can use this property:
Let MySQL set the column when the row is created, which initializes it to the current date and time.
When you perform subsequent changes to other columns in the row, explicitly set the timestamp column as its current value.
On the other hand, you may find it easy to use a datetime column that you initialize with now () when the row is created and away from subsequent changes.
The timestamp value can be from the beginning of one 1970 to 2037, with a precision of one second and its value displayed as a number.
The timestamp value that is retrieved and displayed in MySQL depends on the format of the display size as in the following table. The "full" timestamp format is 14 bits, but the timestamp column can be created with a shorter display size:
Column type display format
TIMESTAMP (+) YYYYMMDDHHMMSS
TIMESTAMP (YYMMDDHHMMSS)
TIMESTAMP (Ten) yymmddhhmm
TIMESTAMP (8) YYYYMMDD
TIMESTAMP (6) YYMMDD
TIMESTAMP (4) yymm
TIMESTAMP (2) YY
All timestamp columns have the same storage size, regardless of the display size. The most common display sizes are 6, 8, 12, and 14. You can specify an arbitrary display size at the table creation time, but the value 0 or greater than 14 is coerced to 14. The odd numeric dimensions from the 1~13 range are coerced to the next larger even number.
You can specify datetime, date, and timestamp values by using any one of the commonly used format sets:
A string of ' yyyy-mm-dd HH:MM:SS ' or ' yy-mm-dd HH:MM:SS ' format. Allows a "loose" syntax-any punctuation can be used as a delimiter between the date part and the time part. For example, ' 98-12-31 11:30:45 ', ' 98.12.31 11+30+45 ', ' 98/12/31 11*30*45 ' and ' [email protected]@31 11^30^45 ' are equivalent.
A string in the ' yyyy-mm-dd ' or ' yy-mm-dd ' format. Allows for a "loose" syntax. For example, ' 98-12-31 ', ' 98.12.31 ', ' 98/12/31 ' and ' [email protected]@31 ' are equivalent.
' Yyyymmddhhmmss ' or ' YYMMDDHHMMSS ' format of a string without any delimiters, for example, ' 19970523091528 ' and ' 970523091528 ' are interpreted as ' 1997-05-23 09:15:28 ', but ' 971122459015 ' is illegal (it has a meaningless minute part) and becomes ' 0000-00-00 00:00:00 '.
The ' YYYYMMDD ' or ' YYMMDD ' format does not have any delimiters for a string if the string is considered to be a date. For example, ' 19970523 ' and ' 970523 ' are interpreted as ' 1997-05-23 ', but ' 971332 ' is not legal (it has meaningless month and day portions) and becomes ' 0000-00-00 '.
A number in the YYYYMMDDHHMMSS or YYMMDDHHMMSS format, if the number is considered to be a date. For example, 19830905132800 and 830905132800 are interpreted as ' 1983-09-05 13:28:00 '.
A number in the YYYYMMDD or YYMMDD format, if the number is considered to be a date. For example, 19830905 and 830905 are interpreted as ' 1983-09-05 '.
A return value can be accepted in a datetime, DATE, or timestamp context function, such as now () or current_date.
Illegal datetime, date or timestamp value is transformed to the appropriate type of "0" value (' 0000-00-00 00:00:00 ', ' 0000-00-00 ' or 00000000000000).
For a value that includes a date part delimiter that is specified as a string, you do not have to specify a 2-digit number for a value less than 10 for a month or day, and ' 1979-6-9 ' is the same as ' 1979-06-09 '. Similarly, for a value that includes a time part delimiter that is specified as a string, you do not have to specify 2 digits for hours, months, or seconds less than 10, and ' 1979-10-30 1:2:3 ' is the same as ' 1979-10-30 01:02:03 '.
Specifies that the number should be 6, 8, 12, or 14 bits long. If the number is 8 or 14 bits long, it is assumed to be in YYYYMMDD or YYYYMMDDHHMMSS format and the year is given by a 4-digit number. If the number is 6 or 12 bits long, it is assumed to be in YYMMDD or YYMMDDHHMMSS format and the year is given by a 2-digit number. Numbers that are not one of these lengths are explained by filling the top 0 to the nearest length.
Strings that are specified as non-delimited are interpreted with their given length. If the length of the string is 8 or 14 characters, the year is assumed to be the first 4 characters, otherwise the year is assumed to be given by a 2 character. For multiple parts rendered in a string, the string is interpreted from left to right to find the year, month, day, hour, minute, and second values, which means that you should not use a string less than 6 characters. For example, if you specify ' 9903 ' that will represent March 1999, you will find MySQL inserting a "0" date into your table, because the year and month values are 99 and 03, but the date part is missing (0), so the value is not a valid date.
The timestamp column uses the full precision of the value that is specified to store the valid value regardless of the display size. Here are a few meanings:
Always specify the year, month, and day, even if your column type is timestamp (4) or timestamp (2). Otherwise, the value will not be a valid date and 0 will be stored.
If you use ALTER TABLE to widen a narrow timestamp column, information that was previously "hidden" will be displayed.
Similarly, shrinking a timestamp column does not cause information to be lost, except that less information is displayed in the sense that the value is displayed.
Although the TIMESTAMP value is stored as full precision, the only function that directly operates on the stored value is Unix_timestamp (), and the other function operates on the formatted retrieved value, which means that you cannot use functions such as hour () or second (), Unless the relevant part of the timestamp value is included in the formatted value. For example, the HH portion of a timestamp column is displayed unless the display size is at least 10, so try using hour () on a shorter timestamp value to produce a meaningless result.
In a way, you can assign a value of a date type to an object of a different date type. However, this may be worth some change or loss of information:
If you assign a date value to a datetime or timestamp object, the time portion of the resulting value is set to ' 00:00:00 ' because the date value does not contain time information.
If you assign a datetime or timestamp value to a Date object, the time portion of the resulting value is deleted because the date type does not store the time information.
Remember that although datetime, date and timestamp values can all be specified with the same set of formats, all types do not have the same range of values. For example, the timestamp value cannot be more than 1970 early or 2037 net late, which means that a date such as ' 1968-01-01 ' is not a correct timestamp value when it is valid as a datetime or date value, and if assigned to such an object, It is to be transformed to 0.
When specifying a date value, beware of certain defects:
A loose format that allows a value to be specified as a string can be spoofed. For example, a value such as ' 10:11:12 ' might look like a time value because the ":" delimiter, but if used in a date, the context will be interpreted as ' 2010-11-12 ' as the year. The value ' 10:45:15 ' will be transformed to ' 0000-00-00 ' because ' 45 ' is not a valid month.
The year value specified with a 2-digit number is ambiguous, because century is unknown. MySQL uses the following rules to interpret the 2-bit year value:
The year value in the 00-69 range is transformed to 2000-2069.
The year value in the 70-99 range is transformed to 1970-1999.
The difference between a date datetime timestamp in a transfer database
