TIMESTAMP usage in mysql

1. TIMESTAMP variants

TIMESTAMP timestamps can have multiple different features during creation, such:

1. Refresh the data column when creating a new record or modifying an existing record:

TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

2. Set this field to the current time when creating a new record, but do not refresh it later:

TIMESTAMP DEFAULT CURRENT_TIMESTAMP

3. Set this field to 0 when creating a new record, and refresh it later:

TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

4. Set this field to a given value when creating a new record, and refresh it later:

TIMESTAMP DEFAULT ‘yyyy-mm-dd hh:mm:ss' ON UPDATE CURRENT_TIMESTAMP

MySQL currently does not support column Default as a function. For example, if the Default value of a column is the current update date and time, you can use the TIMESTAMP column type,The following describes the TIMESTAMP column type in detail.
 
Ii. TIMESTAMP column type

The TIMESTAMP value can start from 1970 to 2037, and is precise to one second. Its value is displayed as a number.
The format of the TIMESTAMP value display size is as follows:
:

+ --------------- + -------------- + | Column type | display format | TIMESTAMP (14) | YYYYMMDDHHMMSS | TIMESTAMP (12) | YYMMDDHHMMSS | TIMESTAMP (10) | YYMMDDHHMM | TIMESTAMP (8) | YYYYMMDD | TIMESTAMP (6) | YYMMDD | TIMESTAMP (4) | YYMM | TIMESTAMP (2) | YY | + ------------- + ---------------- +

The "complete" TIMESTAMP format is 14 bits, but the TIMESTAMP column can also use a shorter display size to create the most common Display sizes 6, 8, 12, and 14.
You can specify an arbitrary display size when creating a table, but the defined column length is 0 or greater than 14, and the column length is forcibly defined as 14.
The column length ranges from 1 ~ The dimensions of odd values in the range of 13 are forced to be the next larger even number.
 
Example:

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. The full precision (14 bits) of the specified period time value is used to store valid values regardless of the display size. An invalid date will be forcibly stored as 0

There are several meanings:

1. although you defined the column TIMESTAMP (8) when creating a table, the TIMESTAMP column actually stores 14 bits of data (including year, month, day, hour, minute, and second) When you insert and update data ), however, during your query, MySQL returns 8-bit year, month, and day data. If you use alter table to broaden a narrow TIMESTAMP column, previously "concealed" information will be displayed.

2. Similarly, narrowing down a TIMESTAMP column will not cause loss of information, except when the upper value is displayed, less information is displayed.

3. although the TIMESTAMP value is stored as the complete precision, the unique function for directly operating the stored value is UNIX_TIMESTAMP (). Because MySQL returns the value of the TIMESTAMP column as the value retrieved after formatting, this means that you may not be able to use certain functions to operate the TIMESTAMP column (such as HOUR () or SECOND () unless the related part of the TIMESTAMP value is included in the formatted value.
For example, if a TIMESTAMP column is defined as TIMESTAMP (10) or higher, the HH part of the TIMESTAMP column is displayed. Therefore, HOUR () is used for shorter TIMESTAMP values () an unpredictable result is generated.

4. the invalid TIMESTAMP value is converted to the "zero" value (00000000000000) of the appropriate type ). (DATETIME, DATE, and so on)

For example, you can use the following statement for verification:

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 | 20021114093723 |+----+----------------+

You can use the TIMESTAMP column type to automatically mark the INSERT or UPDATE operation with the current date and time.
If you have multiple TIMESTAMP columns, only the first one is automatically updated. The first TIMESTAMP column is automatically updated under any of the following conditions:

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 an UPDATE statement and other columns change the value. (Note that setting an UPDATE column as its existing value does not cause the TIMESTAMP column to be updated, because if you set a column as its current value, MySQL ignores the changes for efficiency .)
3. You explicitly set the TIMESTAMP column as NULL.
4. The TIMESTAMP column except the first one can also be set to the current date and time, as long as the column is set to 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 first command sets date1 and date2 to NULL, so the values of date1 and date2 are the values of the current time. The second command does not set the values of date1 and date2 columns, the first TIMESTAMP column date1 is updated to the current time, and the two TIMESTAMP columns date2 change to "00000000000000" because the date is invalid"

UPDATE test SET id= 3 WHERE id=1;+----+----------------+----------------+| id | date1　　　　　| date2　　　　　|+----+----------------+----------------+|　3 | 20021114094009 | 20021114093723 ||　2 | 20021114093724 | 00000000000000 |+----+----------------+----------------+

This command does not explicitly set the column 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 ||　2 | 20021114093724 | 00000000000000 |+----+----------------+----------------+

This command sets date1 = date1, so the value of the date1 column does not change when updating data because date2 = NOW () is set (), therefore, when data is updated, the value of the date2 column is updated to the current time. This command is equivalent:

UPDATE test SET id= 1,date1=date1,date2=NULL WHERE id=3;

Because the TIMESTAMP column returned by MySQL is displayed as a number, you can use the DATE_FROMAT () function to format the TIMESTAMP column, as shown below:

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 some extent, you can assign a value of the date type to an object of different date types.
However, it is important to note that the value may change or cause loss of information:
 
1. if you assign a DATE value to a DATETIME or TIMESTAMP object, the time part of the result value is set to '00: 00: 00' because the DATE value does not contain time information.
2. If you assign a DATETIME or TIMESTAMP value to a DATE object, the time part of the result value is deleted because the DATE type does not store the time information.
3. Although DATETIME, DATE, and TIMESTAMP values can all be specified in the same format set, all types do not have the same value range.

For example,The TIMESTAMP value cannot be earlier than 1970 or later than 2037, which means that a DATE such as '2017-01-01 'is valid when treated as a DATETIME or DATE value, 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.
 
3. When specifying a date value, consider some defects:

1.The loose format that can be specified as a string can be spoofed.For example, because the separator ":" is used, the value '10: 11: 12' may look like a time value, but if it is used in a date, the context is interpreted as the year as '2017-11-12 '. The value '10: 45: 15' is changed to '2017-00-00 'Because '45' is not a valid month.
 
2.The year value specified by a two-digit number is vague because the century is unknown.MySQL uses the following rules to explain the two-digit year value:
The annual value in the range of 2000-69 is changed to 2069. The annual value in the range of 70-99 is changed to 1970-1999.