Comparison of datetime and timestamp in MySQL



This article compares the datetime and timestamp in MySQL with an example, although these two methods actually represent the same type, but there are some differences.

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

I am on mysql5.3, using the NVICAT client append column, view SQL text, found by default as follows:

' xxx ' timestamp null DEFAULT null on UPDATE current_timestamp

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

(Note that there is no such check in the case of DB Sql_mode, not strict mode)

Default Current_timestamp

Default Current_timestamp on UPDATE current_timestamp

On UPDATE Current_timestamp

Write more errors

Comparison of datetime and timestamp in MySQL