Comparison between Datetime and Timestamp in Mysql, datetimetimestamp

Source: Internet
Author: User

Comparison between Datetime and Timestamp in Mysql, datetimetimestamp
 

In mysql, the three types of date, datetime, and timestamp (if int is used, four types) are easy to confuse. The following compares the similarities and differences between the three types of time.

Similarities

Can be used to indicate the time
Are displayed as strings

Differences

1. as the name suggests, date only represents the date in the form of 'yyyy-MM-DD ', datetime represents the date plus time in the form of 'yyyy-MM-DD HH: mm: ss', timestamp and datetime display form is the same.
2. the time range that date and datetime can represent is '2017-01-01 'to '2017-12-31'. timestamp is limited by 32-bit int type, it indicates the UTC time between '2017-01-01 00:00:01 'and '2017-01-19 03:14:07.
3. When mysql stores the timestamp type, it converts the time to UTC time, and then restores it to the current time zone when reading the data. If you have stored a value of the timestamp type and modified the mysql time zone, you will get an error time when you read the value again. This does not happen in date and datetime.
4. The timestamp type provides the automatic update function. You only need to set its default value to CURRENT_TIMESTAMP.
5. Except that date is retained to the day, datetime and timestamp are retained to the second, while millisecond is ignored.

Time Format

Mysql provides a relatively loose time string format for addition, deletion, modification, and query. Refer to the iso time format. Generally, it is used to writing '2017-06-05 16:34:18 '. However, you can also name it '13-6-5 ', but this is easy to cause confusion. For example, mysql also treats '13: 6: 5' as year, month, and day, while '13: 16: 5' is considered incorrect by mysql. A warning is given and the value stored in the database is '2017-00-00 00:00:00 '.

The Manual also specifically mentions a situation where the value of the current year is 0 ~ 69, mysql considers it as 2000 ~ 2069, and 70 ~ 99 is considered as 1970 ~ 1999. I feel like it is a superfluous thing.

In short, using the 'yyyy-MM-DD HH: mm: ss' format will always be fine

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.