Shulou(Shulou.com)05/31 Report--

This article shows you what the role of TIMESTAMP in mysql is, the content is concise and easy to understand, it can definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Variants of TIMESTAMP

1,TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Refresh this data column when you create a new record and modify an existing record

2 DEFAULT CURRENT_TIMESTAMP TIMESTAMP created a new record with this

Field is set to the current time, but when modified later, it will not be refreshed

3The timestamp ON UPDATE CURRENT_TIMESTAMP sets this field to 0 when creating a new record

Refresh it www.2cto.com when you modify it later

4The 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 does not support the Default of a column as a function. For example, the default value of a column is the current update date.

With the function of time, you can use the TIMESTAMP column type. The TIMESTAMP column type is described in detail below.

* TIMESTAMP column type *

The TIMESTAMP value can run from sometime in 1970 to 2037 with a precision of one second, and its value is displayed as a number.

The format of the TIMESTAMP value display size 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 TIMESTAMP columns can also be displayed in a shorter size.

The most common display sizes created are 6, 8, 12, and 14.

You can specify any display size when creating a table, but defining a column length of 0 or greater than 14 will be forced to be defined as a column length of 14.

Column lengths in the range from 1 to 13 are forced to the next larger even number.

* listed as follows: *

Define the field length to force the 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, using the full precision of the specified time value

(14-bit) storing legal values regardless of the display size. Illegal date will be forced to be stored as 0

* this has several implications: * www.2cto.com

1. Although column TIMESTAMP (8) is defined when you create the table, TIMESTAMP column is defined when you insert and update data

It actually saves 14 bits of data (including the year, month, day, hour and second), but when you query, MySQL returns to

Yours is 8-digit data of the year, month and day. If you use ALTER TABLE to widen a narrow TIMESTAMP column

Previously "hidden" messages will be displayed.

2. Similarly, shrinking a TIMESTAMP column does not cause information to be lost, except that the perceived upper value is displayed.

Less information is displayed.

3. Although the timestamp value is stored as full precision, the only function that directly manipulates the stored value is UNIX_TIMESTAMP ()

Since the column value returned by MySQL for 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 (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 will be displayed only if it is defined as more than TIMESTAMP (10).

So using HOUR () on a shorter timestamp value produces an unpredictable result.

4. The illegal timestamp value is converted to a zero value of the appropriate type (00000000000000). (so does DATETIME,DATE)

* 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 INSERT or UPDATE operations with the current date and time.

If you have more than one TIMESTAMP column, only the first one is automatically updated. Automatic updating 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 one UPDATE statement and other columns change the value. (note a UPDATE

Setting the value of a column that it already has will not cause the TIMESTAMP column to be updated, because if you set a column

Its current value, MySQL ignores changes for efficiency. )

3. You explicitly set TIMESTAMP as NULL.

4. TIMESTAMP columns other than the first 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 sets date1 and date2 to NULL, so the values of date1 and date2 are the second instruction at the current time.

Since no date1 and date2 column values are set, the first TIMESTAMP column date1 is updated to the current time

While the two TIMESTAMP columns date2 become "00000000000000" because the date is illegal.

UPDATE test SET id= 3 WHERE id=1

+-- +

| | id | date1 | date2 | |

+-- +

| | 3 | 20021114094009 | 20021114093723 |

| | 2 | 20021114093724 | 00000000000000 |

+-- +

This directive 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, date 1, date 2, now () WHERE id=3; www.2cto.com

+-- +

| | id | date1 | date2 | |

+-- +

| | 1 | 20021114094009 | 20021114094320 |

| | 2 | 20021114093724 | 00000000000000 |

+-- +

This instruction sets date1=date1 so that the value of the date1 column does not change when the data is updated.

Because date2=NOW () is set, the date2 column value is updated to the current time when the data is updated. This instruction is equivalent to

UPDATE test SET id= 1, date 1, date 2, 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

SELECT id,DATE_FORMAT (date1,'%Y-%m-%d% HRV% iRU% s') As date1

DATE_FORMAT (date2,'%Y-%m-%d% HRV% iRU% 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-0000: 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 | |

+-- +

To some extent, you can assign a value of one date type to an object of a different date type.

However, it is particularly important to note that there may be some changes in values 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 _ 0 _ 0 _ 0 _ 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 time information.

3. Although DATETIME, DATE and timestamp values can all be specified in the same format set

But not all types have the same range of values.

For example, the timestamp value cannot be earlier than 1970 or later than 2037, which means that a date such as' 1968-01-01'

It is legal 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 transformed to 0. Www.2cto.com

* when specifying a date value, beware of some defects: *

1. A loose format that is allowed to be specified as a string can be deceived. For example, because of the use of the ":" delimiter

A value of'10, v, d, and so on, but if used in a date, the context will be used as the year.

Explain it as' 2010-11-12'. The value of'10 45 15 'will be converted to' 0000-00-00 month, because'45'is not a legal month.

2. The year value specified in two digits is vague because the century is unknown. MySQL uses the following rules to interpret 2-digit annual values:

The annual value in the range of 00-69 is converted to 2000-2069. The annual value in the range 70-99 is converted to 1970-1999.

The above content is what is the role of TIMESTAMP in mysql. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.