Similarities and differences of datetime and timestamp in MySQL

Same

Show

The timestamp column displays the same format as 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

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

Stores

TIMESTAMP

1.4-byte storage (time stamp value was stored in 4 bytes)

2. Value is saved in UTC (It stores the number of milliseconds)

3. Time zone conversion, storage time for the current time zone conversion, retrieval and then converted back to the current time zone.

Datetime

1.8 bytes Storage (8 bytes storage)

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

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

Example comparison

Now I'm going to do a time zone on their influence.

1. Inserts a data insert INTO ' T8 ' values First (now (), now ());

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

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