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

This article introduces how to compare time types in mysql. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

The same

Display

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

Different

Range

Datetime retrieves and displays data values in 'YYYY-MM-DD HH:MM:SS' format. The range of supported values is' 1000-01-01 00 purve00' to '9999-12-31 23 purve59' timestamp values cannot be earlier than 1970 or later than 2037

Storage

TIMESTAMP

1.4byte storage (Time stamp value is stored in 4 bytes)

two。 Values are saved in UTC format (it stores the number of milliseconds)

3. Time zone conversion, the current time zone is converted when storing, and then back to the current time zone when retrieving.

Datetime

1.8byte storage (8 bytes storage)

two。 Actual format storage (Just stores what you have stored and retrieves the same thing which you have stored.)

3. Has nothing to do with time zone (It has nothing to deal with the TIMEZONE and Conversion.)

Case comparison

Now I'll do a time zone effect on them.

1. First insert a data insert into `t8` values (now (), now ())

two。 Change the client time zone (East 9, Japan time zone).

3. The inserted data is displayed again. The data of timestamp type has been increased by 1 hour.

Next, we will discuss some other properties of timestamp

Whether 1.null is empty

Timestamp is allowed to be "not null by default" by default, if you are defining "ts TIMESTAMP DEFAULT NULL" is illegal. You can specify empty null, "ts TIMESTAMP NULL", and 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 choose one of the following)

Default CURRENT_TIMESTAMP

Default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

ON UPDATE CURRENT_TIMESTAMP

ON UPDATE see above 2

On how to compare time types in mysql to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.