About Datetime,date,timestamp,year,time Time Types summary

about the Datetime,date,timestamp,year,time Time Type

datetime occupy 8 of bytes

Date range: "1000-01-01 00:00:00" to "9999-12-31 23:59:59"

Date occupy 3 of bytes

can display range: "1000-01-01" to "9999-12-31"

timestamp occupy 4 of bytes

Timestamp display results as datetime, fixed format: "Yyyy-mm-dd HH:MM:SS"

The difference is:

The 1,timestamp occupies 4 bytes, showing the range "1970-01-01 00:00:00" to "2038-01-19 03:14:17".

2, when building a table, the date type listed as timestamp can be set to a default value, and DateTime does not.

3, when you update the table, you can set the timestamp type of column to automatically update to the current time.

4, in a table, only one row of the specified time type can be specified as "TIMESTAMP NOT NULL default Current_timestamp", and the time type of other columns needs to be specified as "TIMESTAMP NOT NULL default" 0000-00-00 00:00:00 ' ".

Example 1:

CREATE TABLE T (a int,b TIMESTAMP DEFAULT current_timestamp) Engine=innodb Charset=utf8;

SHOW CREATE TABLE t;

INSERT into T (a) VALUES (1);

[Email protected] 17:04>select * from t;

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

| A | B |

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

| 1 | 2012-12-13 17:03:58 |

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

Example 2:

CREATE TABLE ' T2 ' (

' A ' INT (one) DEFAULT NULL,

' B ' TIMESTAMP not NULL DEFAULT current_timestamp on UPDATE current_timestamp

) Engine=innodb DEFAULT Charset=utf8;

Inserting data

[email protected] 17:07>insert into T2 select 1,current_timestamp;

Query OK, 1 row Affected (0.00 sec)

Records:1 duplicates:0 warnings:0

[Email protected] 17:07>select * from T2;

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

| A | B |

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

| 1 | 2012-12-13 17:07:25 |

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

1 row in Set (0.00 sec)

Just wait a little bit.

[email protected] 17:07>update T2 set a=100;

Query OK, 1 row Affected (0.00 sec)

Rows matched:1 changed:1 warnings:0

[Email protected] 17:08>select * from T2;

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

| A | B |

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

| 100 | 2012-12-13 17:08:16 |

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

1 row in Set (0.00 sec)

After the update operation is discovered, the value of column A is changed, and column B has the original "2012-12-13 17:07:25" updated to "2012-12-13 17:08:16".

If the update operation updates the value of column A and does not actually alter any of the data in the row, then column B does not operate accordingly. Take a look at example 3.

Example 3:

[Email protected] 17:08>select * from T2;

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

| A | B |

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

| 100 | 2012-12-13 17:08:16 |

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

1 row in Set (0.00 sec)

[email protected] 17:11>update T2 set a=100;

Query OK, 0 rows Affected (0.00 sec)

Rows matched:1 changed:0 warnings:0

[Email protected] 17:11>select * from T2;

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

| A | B |

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

| 100 | 2012-12-13 17:08:16 |

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

1 row in Set (0.00 sec)

Summary: When using the timestamp type, the program is assigned because the current time in the program is not NULL. When formulating the TIMESTAMP type, do not specify "TIMESTAMP not NULL DEFAULT current_timestamp on UPDATE current_timestamp" and specify " TIMESTAMP not NULL DEFAULT current_timestamp ". This will not cause changes in time to update the data.

Year and the Time type

Year type only occupies 1 bytes , for year (4), the display year range of 1901-2155;year (2) shows the years range of 1970-2070.

Time type Occupancy 3 of bytes

Summary: The time type is based on the principle of sufficiency and can be used timestamp don't use it . datetime , you can use Year , don't use it. datetime, or Date type.

About Datetime,date,timestamp,year,time Time Types summary