Comparison between datetime and timestamp in MySQL

The display format of the same TIMESTAMP column is the same as that of the DATETIME column. In other words, the display width is fixed to 19 characters and the format is YYYY-MM-DDHH: MM: SS. Different ranges of datetime are retrieved and displayed in YYYY-MM-DDHH: MM: SS format. Supported range: 1000-01-0100:00:00 to 9999-12-3123: 59: 59 T

The display format of the same TIMESTAMP column is the same as that of the DATETIME column. In other words, the display width is fixed to 19 characters and the format is YYYY-MM-DD HH: MM: SS. Datetime values of different ranges are retrieved and displayed in 'yyyy-MM-DD HH: MM: ss' format. Supported range: '2017-01-01 00:00:00 'to '2017-12-31 23:59:59' T

Same

Display

The display format of the TIMESTAMP column is the same as that of the DATETIME column. In other words, the display width is fixed to 19 characters and the format is YYYY-MM-DD HH: MM: SS.

Different

Range

DatetimeRetrieves and displays DATETIME values in 'yyyy-MM-DD HH: MM: ss' format. Supported range: '2017-01-01 00:00:00 'to '2017-12-31 23:59:59'TIMESTAMPThe value cannot be earlier than 1970 or later than 2037

Storage

TIMESTAMP

Storage of 1.4 bytes (Time stamp value is stored in 4 bytes)

2. Save the value in UTC format (it stores the number of milliseconds)

3. Time Zone conversion. during storage, the current time zone is converted, and then retrieved to the current time zone.

Datetime

1.8 bytes (8 bytes storage)

2. Storage in actual format (Just stores what you have stored and retrieves the same thing which you have stored .)

3. It has nothing to deal with the TIMEZONE and Conversion .)

Instance comparison

Now let's make a time zone impact on them.

1. insert a data insert into 't8 'values (now (), now ());

2. Change the client Time Zone (East 9, Japan time zone ).

3. display the inserted data again. The changed data is of the timestamp type.Increased by 1 hour

498) this. width = 498; 'onmousewheel = 'javascript: return big (this) 'class = blogimg border = 0 src = "/files/uploadimg/20090518/1404280 .jpg" small =" 0 ">

Next we will discuss other timestamp attributes.

1. Whether null is null

The default value of timestamp is "not null by DEFAULT". It is invalid if you define "ts TIMESTAMP default NULL. You can specify null and "ts timestamp null". In this case, you can change the default value in the add statement.

ts2 TIMESTAMP NULL DEFAULT 0, ts3 TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP

Default (only one column in a table can be selected)

Default CURRENT_TIMESTAMP Default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP On update CURRENT_TIMESTAMP On update can be found at 2