Comparison of datetime and timestamp in MySQL

Turn from:datetime vs. timestamp in MySQL

Same

1. 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

1. Scope

datetime Retrieves and displays datetime values in the ' yyyy-mm-dd HH:MM:SS ' format. The range of support is ' 1000-01-01 00:00:00 ' to ' 9999-12-31 23:59:59 '.

TIMESTAMP value cannot be earlier than 1970 or later than 2037

2. Storage

TIMESTAMP

1.4 Bytes of storage (time stamp value is stored in 4 bytes)

2. Values are saved in UTC (It stores the number of milliseconds)

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

Datetime

1.8 bytes of storage (8 bytes storage)

2. Actual format storage (Just stores what are you having stored and retrieves the same thing which you have stored.)

3. Time zone Independent (It has nothing to deal with the TIMEZONE and Conversion.)

Example comparison

Now I'm going to make a time zone impact on them.

1. Insert a data into the ' T8 ' Values (now (), now ());

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

3. Display the inserted data again, changed, timestamp type of data increased by 1 hours

Comparison of datetime and timestamp in MySQL