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

This article is to share with you about the role of datetime and timestamp in mysql, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

DATETIME: a combination of date and time. The range of support is from '1000-01-01 00' to '9999-12-31 23 59'. MySQL displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format, but allows values to be assigned to DATETIME columns using strings or numbers.

TIMESTAMP [(M)]: timestamp. The range is from '1970-01-01 00 to 2037.

The TIMESTAMP column is used to record the date and time during an INSERT or UPDATE operation. If you do not assign a value, the first TIMESTAMP column in the table is automatically set to the date and time of the most recent operation. You can also set the TIMESTAMP column to the current date and time by assigning a null value. 、

Examples are as follows:

You can set default values for TIMESTAMP types in MYSQL, just like other types.

1. Automatic UPDATE and INSERT to the current time:

Table:

CREATE TABLE `t1` (

Pascal int (11) NOT NULL

P_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=gb2312

Data:

1 2007-10-08 11:53:35

2 2007-10-08 11:54:00

Insert into T1 (pamphc) select 3

Update T1 set Prunc = 2 where Prunc = 5

Data:

1 2007-10-08 11:53:35

5 2007-10-08 12:00:37

3 2007-10-08 12:00:37

2. Automatically INSERT to the current time, but not automatically UPDATE.

Table:

CREATE TABLE `t2` (

`pc` int (11) NOT NULL

`ptime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

) ENGINE=InnoDB DEFAULT CHARSET=gb2312

Data:

Insert into T2 (Prunc) select 4

Update T2 set Prunc = 3 where Prunc = 5

1 2007-10-08 11:53:35

2 2007-10-08 12:00:37

5 2007-10-08 12:00:37

4 2007-10-08 12:05:19

3. You cannot have two fields in a table whose default value is the current time, otherwise an error will occur. But others can.

Table:

CREATE TABLE `t1` (

`pc` int (11) NOT NULL

`ptime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

`ptimew2` timestamp NOT NULL DEFAULT '0000-00-0000: 0000'

) ENGINE=InnoDB DEFAULT CHARSET=gb2312

Data:

1 2007-10-08 11:53:35 0000-00-0000: 00:00

2 2007-10-08 12:00:37 0000-00-0000: 00:00

3 2007-10-08 12:00:37 0000-00-0000: 00:00

4 2007-10-08 12:05:19 0000-00-0000: 00:00

The TIMESTAMP value is returned and displayed as a string in 'YYYY-MM-DD HH:MM:SS' format, with a fixed display width of 19 characters. If you want to get a numeric value, add + 0 to the TIMESTAMP column.

These are the functions of datetime and timestamp in mysql. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please 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.