Comparison of datetime and timestamp in MySQL

Same

Show

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 datetime values in the ' yyyy-mm-dd HH:MM:SS ' format. The supported range 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 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

Next discuss some of the other properties of timestamp

1.null is empty

Timestamp default allows for "non-null" (NOT NULL by default), if you are in the definition "TS timestamp default NULL" is illegal. can be specified as NULL null, "TS TIMESTAMP null", at which point the default value can be changed in the Add statement.

TS2 TIMESTAMP NULL DEFAULT 0,

Default (only one column in a table selects one of the following)

Default Current_timestamp Default Current_timestamp on UPDATE current_timestamp On UPDATE Current_timestamp On UPDATE See 2

Comparison of datetime and timestamp in MySQL