MySQL TIMESTAMP (timestamp) detailed

Variants of the timestamp

1,timestamp DEFAULT current_timestamp on UPDATE current_timestamp
Refreshes the data column when new records are created and existing records are modified

2,timestamp DEFAULT Current_timestamp This when creating a new record
field is set to the current time, but later modified, it is no longer refreshed

3,timestamp on UPDATE current_timestamp set this field to 0 when creating a new record.
Refresh it later when modified

4,timestamp DEFAULT ' Yyyy-mm-dd hh:mm:ss ' on UPDATE current_timestamp
Set this field to the given value when you create a new record, and refresh it later when you modify it

MySQL currently does not support column default as a function form, such as reaching you with a column that defaults to the current update date
With the function of time, you can use the timestamp column type below to specify the timestamp column type

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

：

+---------------+----------------+

| 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 "full" timestamp format is 14 bits, but the timestamp column can also be displayed with a shorter display size
The most common display sizes created are 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.

* Listed as: *

Define field length force field length

TIMESTAMP (0), TIMESTAMP (14)

TIMESTAMP (TIMESTAMP) (14)

TIMESTAMP (1), TIMESTAMP (2)

TIMESTAMP (5), TIMESTAMP (6)

All timestamp columns have the same storage size, using the complete precision of the specified time period value
(14-bit) stores a valid value regardless of the display size. Illegal date, will be forced to 0 storage

* This has several implications:

1, although you define the table when the column timestamp (8), but when you do data insertion and update timestamp column
In fact, 14 bits of data are saved (including date and time of day and minute), but MySQL is returned to you when you query
Yours is the 8-digit day date data. 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 in the sense that the value is displayed,
Less information is displayed.

3, although the TIMESTAMP value is stored as full precision, the only function to manipulate the stored value directly is Unix_timestamp ();
Because MySQL returns the column value of the timestamp column is the retrieved value after formatting, this means that you may not be able to use some functions to manipulate the timestamp column (for example, 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 only if a timestamp column is defined as timestamp (10).
Therefore, 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)

* You can use the following statements 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 | 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. 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
Set a column to the value it already has, which will not cause timestamp columns to be updated, because if you set a
Its current value, and MySQL ignores changes for efficiency. ）

3. You explicitly set the timestamp column to be null.

4. Timestamp columns 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 ().

CREATE TABLE Test (www.2cto.com

' 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 and Date2 values are the current time the second instruction
Because there is no 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

UPDATE Test SET id= 3 WHERE id=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

UPDATE Test SET id= 1,date1=date1,date2=now () WHERE id=3; Www.2cto.com

+----+----------------+----------------+

| ID | Date1 | Date2 |

+----+----------------+----------------+

| 1 | 20021114094009 | 20021114094320 |

| 2 | 20021114093724 | 00000000000000 |

+----+----------------+----------------+

This directive is set date1=date1, so date1 column values do not change when updating data
Because the Date2=now () is set, the Date2 column value is updated to the current time when the data is updated this directive is equivalent to
UPDATE test SET id= 1,date1=date1,date2=null WHERE id=3;

You can use the Date_fromat () function to format the TIMESTAMP column because the TIMESTAMP returned by MySQL is listed as a digital display

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;

Www.2cto.com

+----+-------------+-------------+

| 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 time information is not included in the date value.

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 time information.

3, although datetime, date and timestamp values can all be specified with the same format set,
But all types do not have the same range of values.

For example, the timestamp value cannot be more than 1970, nor can it be compared to 2037 nights, which means that a date such as ' 1968-01-01 ',
It 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. Www.2cto.com

* When specifying a date value, beware of certain defects: *

1. A loose format that allows a value to be specified 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 '. 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, 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 range 70-99 is transformed to 1970-1999.