Comparison of datetime and timestamp in MySQL

Source: Internet
Author: User
Tags japan time zone

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.