MySQL timestamp (timestamp) usage

I. Variants of TIMESTAMP

Timestamp timestamps can have multiple different features when created, such as:

1. Refresh the data column when creating new records and modifying existing records:

?

1	TIMESTAMPDEFAULT CURRENT_TIMESTAMP ON UPDATECURRENT_TIMESTAMP

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

?

1	TIMESTAMPDEFAULTCURRENT_TIMESTAMP

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

?

1	TIMESTAMPON UPDATECURRENT_TIMESTAMP

4. When creating a new record, set this field to the given value and refresh it later when it is modified:

?

1	TIMESTAMPDEFAULT ‘yyyy-mm-dd hh:mm:ss‘ ON UPDATECURRENT_TIMESTAMP

MySQL currently does not support column default as a function, such as the default value of your column to the current update date and time function, you can use the timestamp column type, the following detailed description timestamp column type

Second, timestamp column type

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 format of the timestamp value display dimension is shown in the following table:
：

?

12345678910

+---------------+----------------+| 列类型　　　　| 显示格式　　　 || TIMESTAMP(14) | YYYYMMDDHHMMSS |　| TIMESTAMP(12) | YYMMDDHHMMSS　 || TIMESTAMP(10) | YYMMDDHHMM　　 || TIMESTAMP(8)　| YYYYMMDD　　　 || TIMESTAMP(6)　| YYMMDD　　　　 || TIMESTAMP(4)　| YYMM　　　　　 || TIMESTAMP(2)　| YY　　　　　　 |+---------------+----------------+

The "full" timestamp format is 14-bit, but the timestamp column can also be used for shorter display sizes, creating the most common display sizes of 6, 8, 12, and 14.
You can specify an arbitrary display size when creating a table, but defining a column length of 0 or greater than 14 is mandatory to define column length 14.
The odd numeric dimensions of the column length in the range from 1~13 are coerced to the next larger even number.

Gca

?

12345

定义字段长度　　 强制字段长度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 full precision (14-bit) of the specified period time value is used to store the valid value regardless of the display size. Illegal date, will be forced to 0 storage

Here are a few meanings:

1. Although you define column timestamp (8) When you create a table, the timestamp column actually holds 14 bits of data when you insert and update the data (including date and time of month and minute), except that MySQL returns you with 8-digit date data when you query. If you use ALTER TABLE to widen a narrow timestamp column, information that was previously "hidden" will be displayed.

2. Similarly, narrowing a timestamp column does not result in information loss, except when the value is displayed, less information is displayed.

3. Although the TIMESTAMP value is stored as full precision, the only function that directly operates to store the value is Unix_timestamp (); Since MySQL returns the column value of the TIMESTAMP column is the retrieved value after the formatted This means that you may not be able to use certain functions to manipulate timestamp columns (such as hour () or second ()) unless the relevant part of the timestamp value is included in the formatted value.
For example, if a timestamp column is defined above timestamp (10), the HH portion of the timestamp column is displayed, so using hour () on a shorter timestamp value produces an unpredictable result.

4. The illegal timestamp value is transformed to the appropriate type of "0" value (00000000000000). (Datetime,date and vice versa)

For example, you can use the following statements to verify:

?

12345678910111213141516

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 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:

1. The column values are not explicitly specified in an INSERT or load DATA infile statement.
2. Column values are not explicitly specified in an UPDATE statement and some other 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.) ）
3. You explicitly set the timestamp column to be 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 ().

?

1234567891011121314

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 instruction is set date1, Date2 is null, so the date1, date2 values are the current time the second instruction because there is no set date1, date2 column value, the first timestamp column date1 is updated to the current time, and two timestamp columns Date2 changed to "00000000000000" due to the illegal date

?

1234567

UPDATEtest SET id= 3 WHEREid=1;+----+----------------+----------------+| id | date1　　　　　| date2　　　　　|+----+----------------+----------------+|　3 | 20021114094009 | 20021114093723 ||　2 | 20021114093724 | 00000000000000 |+----+----------------+----------------+

This directive does not explicitly set Date2 column values, so the first timestamp column date1 will be updated to the current time

?

1234567

update test set id= 1,date1=date1,date2=now () where id=3; + ----+----------------+----------------+ | ID | date1 | date2 | + ----+----------------+----------------+ | 1 | 20021114094009 | 20021114094320 | | 2 | 20021114093724 | 00000000000000 | + ----+----------------+----------------+

This instruction is set to Date1=date1, so the Date1 column value does not change when updating the data and is set Date2=now (), so when the data is updated the Date2 column value is updated to the current time this directive is equivalent to:

?

1	UPDATEtest SET id= 1,date1=date1,date2=NULL WHEREid=3;

Because the TIMESTAMP returned by MySQL is listed as a digital display, you can use the Date_fromat () function to format the TIMESTAMP column as follows:

?

12345678910111213141516

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

In a way, you can assign a value of a date type to an object of a different date type.
However, it is particularly important to note that there may be some change in value or loss of information:

1. 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.
2. 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.
3. 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, thetimestamp value cannot be more than 1970 or 2037 nights, which means that a date such as ' 1968-01-01 ' is legal when it is 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 transformed to 0.

Third, when specifying a date value, beware of certain defects:

1. allow loose formatting that specifies a value as a string can be spoofed. For example, because the ":" Delimiter is used, the value ' 10:11:12 ' may look like a time value, 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.

2. the year value specified in 2 digits is ambiguous, since 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 range 70-99 is transformed to 1970-1999.

MySQL timestamp (timestamp) usage