Similarities and differences of datetime and timestamp in MySQL

Source: Internet
Author: User
Tags current time datetime japan time zone 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

Related Article

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.