TimeStAMP column type detailed (how to get the default value is now ())
MySQL currently does not support the form of default as a function, such as reaching the default value of your column for the current update date and time function, you can use the TimeStAMP column type to specify the TimeStAMP column type
The TimeStAMP column type TIMESTAMP value can be from 1970 to 2037, and the accuracy is one second, and its value is a digital display. TimeSTAMP Value Displays the size format as shown in the following table:: ---------------------------- | Column Type | Display format || TimeSTAMP (14) | YYYYMMDDHHMMS | | TimeStamp (12) | YYMMDDHMMS || TimeStamp (10) | YYMMDDHMM || TimeStamp (8) | YYYYMMDD || TimeStamp (6) | YYMMDD || TimeStamp (4) | YYMM || TimeStamp (2) | YY | -------------------------------- "Complete" TimeStamp format 14 Bit, but the TimeStAMP column can also create the most common display size of 6, 8, 12, and 14 in shorter display sizes. You can specify an arbitrary display size when you create a table, but define the collar length of 0 or more will be mandatory as the column length 14. The odd-numbered dimensions of the columns from 1 to 13 are forced to be the next larger even.
Such as: Define Field Length Force Field Length TimeStamp (0) -> TimeSTAMP (14) TimeStamp (15) -> TimeStamp (14) TimeStamp (1) -> TimeStamp (2) TimeStamp (5) -> TimeStamp (6)
All TimeStAMP columns have the same storage size, and the display size is displayed using the full precision of the scheduled time value (14-bit). If you are not legal, it will be mandatory to store this there are several meaning: 1. Although you define column TimeStamp (8) when you build a table, the timestamp column is actually saved 14 digits when you perform data insertion and update. Data (including the year-time daytime or second), but mysql returns to you is 8-bit annual data. If you use ALTER TABLE to broaden a narrow TIMESTAMP column, the information previously "hidden" will be displayed. 2. Similarly, narrowing a TimeStAMP column does not cause information to be lost, in addition to the value of the value, less information is displayed. 3, although the timestamp value is stored as a complete precision, the unique function of the direct operation stored value is unix_timestamp (); because the value of MySQL returns the TimeStAMP column is the value of the retrieved retrieval, this means you may not be able to use some Some functions to operate the TimeStAMP column (for example, Hour () or second ()), unless the relevant portion of the TimeStAmp value is included in the formatted value. For example, a TimeStAMP column is only displayed when it is defined as timestamp (10), and the TimeStAMP column will be displayed, so it is unpredictable to generate an unpredictable result on a shorter TimeStamp value. 4, the illegal TimeStamp value is transformed to the appropriate type of "zero" value (00000000000000). (DateTime, Date) You can use the following statement to verify: create Table Test ('id' int (3) unsigned auto_increment, 'Date1' TimeStamp (8) Primary Key ('ID')); Insert Into Test Set ID = 1; select * from test; - ---------------- | ID | DATE1 | ---- -------- -------- | 1 | 20021114 | ---- ---------------- Alter Table Test Change 'Date1' Date1 'TimeStamp (14 ); Select * from test; ---- -------------- | ID | DATE1 | ---- ------- ------- | 1 | 2002114093723 | ---- ----------------
You can automatically use the TimeStAMP column type to automatically use the current date and time to mark the operation of INSERT or UPDATE. If you have multiple TimeStAMP columns, only the first automatic update. Automatic Update First TimeStAMP column happens under any of the following:
1. The column value is not explicitly specified in an INSERT or LOAD DATA INFILE statement. 2, the column value is not explicitly specified in a UPDATE statement and other columns change the value. (Note that a UPDATE sets a value listed as it already, this will not cause the TimeStAMP column to be updated, because if you set a list of current values, MySQL ignore changes in the efficiency.) 3, you clearly set TimeSTAMP is listed as NULL. 4, in addition to the TimeStAMP column other than the first time, can also be set to the current date and time, as long as NULL, or now (). Create Table Test ('id' int (3) unsigned auto_increment, 'Date1' TimeStamp (14), 'Date2' TimeStamp (14), Primary Key ('ID'));
INSERT INTO TEST (ID, DATE1, DATE2) VALUES (1, null, null); Insert INTO TEST SET ID = 2; ---- -------------- ---------------- | ID | DATE1 | DATE2 | ---- -------------- --- ------------- | 1 | 20021114093723 | 20021114093723 || 2 | 20021114093724 | 00000000000000 | ---- -------------- ---------------- -> The directive is Date1, Date2 is NULL, so Date1, Date2 value is the current time second instruction because there is no Date1, Date2 column value, the first timestamp column Date1 is updated to the current time, and the two timestamp columns Date2 becomes "0000000000000000" because the date is not legal.
Update test set id = 3 where id = 1; ---- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ID | DATE1 | DATE2 | ---- ---------------- -------------- | 3 | 20021114094009 | 20021114093723 || 2 | 20021114093724 | 00000000000000 | ---- ------------------------------- - -> This instruction does not explicitly set the value of DATE2, so the first timestamp column Date1 will be updated to the current time.
Update test set id = 1, date1 = DATE1, date2 = now () where id = 3; - -------------- ------ ---------- | ID | DATE1 | DATE2 | ---- -------------- --------- ------- | 1 | 20021114094009 | 20021114094320 | 2002114094320 || 2 | 20021114093724 | 0000000000000000 | ---- ---------------- ----- ---------- -> This command is set for Date1 = DATE1, so the date1 column value does not change when updating data is changed and Date2 = now (), so in update When the data is updated to the current time this command is equivalent to update test set id = 1, Date1 = Date1, Date2 = NULL WHERE ID = 3; Date_Fromat you can use due to mySQL () Function to format the TimeStamp column Select ID, DATE_FORMAT (DATE1, '% Y-% M-% D% H:% i:% s') as date1, date_format (date2,'% y-% M-% D) % H:% i:% s') as date2 from test; - --------------------------- -------------- | ID | DATE1 | DATE2 | ---- --------------------- --------------------- | 1 | 2002-11-14 09:40:09 | 2002-11-14 09:43:20 || 2 | 2002-11-14 09:37:24 | 0000-00-00 00:00:00 | - --------------------- ---------------------
SELECT ID, DATE_FORMAT (DATE1, '% Y-% M-% D') AS DATE1, DATE_FORMAT (DATE2, '% Y-% M-% D') as date2 from test; ---- --- ---------- ------------- | ID | DATE1 | DATE2 | ---- ---------- - ------------- | 1 | 2002-11-14 | 2002-11-14 || 2 | 2002-11-14 | 0000-00-00 | --- - ------------ -------------
To a certain extent, you can assign a date type of value to a different date type of object. However, especially note that values may have some changes or information loss:
1. If you assign a DateTE value to a DateTime or TimeStamp object, the time portion of the result value is set to '00: 00: 00 ', because there is no time information in the DATE value. 2, if you assign a DateTime or TimeStamp value to a Date object, the time portion of the result value is deleted because the Date type does not store the time information. 3, although DateTime, Date and TimeStamp values can be specified in the same format, but all types do not have the same value range. For example, the timestamp value cannot be earlier than 1970, it is not more than 2037 nights, which means that a date is, for example, '1968-01-01', when it is a DateTime or Date value it is legal, but it is not a correct TimeStamp value ! And if such an object is assigned to the TimeSTAMP column, it will be converted to 0. When specifying the date value, beware of some defects:
1. Allowing the loose format as a string specified value to be deceived. For example, because ":" The use of the separator, value '10: 11: 12' may look like time value, but if used in a date, the context will be interpreted as a year to '2010-11-12'. Value '10: 45: 15' will be transformed to '0000-00-00' because '45' is not a legitimate month. 2. The annual value specified in the 2 digit is blurred because the century is unknown. MySQL uses the following rules to explain the 2-bit annual value: the annual value of the range of 00-69 is transformed to 2000-2069. The annual value of 70-99 is transformed to 1970-1999.
