Those things about MySQL (2) How time type data is stored

Source: Internet
Author: User

Almost every time the database is modeled, there is a problem with the type of data storage.

MySQL storage time typically chooses these four types: datetime, timestamp, int and bigint four ways, exactly what type to use, need to look at the specific business.

We discuss each of these four types separately.

1. DateTime

This type can store 8 bytes, representing data from 1000-9,999. This type can be either a null value or a custom value, and the default initial value is "0000-00-00 00:00:00".

The advantage of storing this type of storage is that the data is readable and does not require a function to be converted.

2, timestamp

This type of storage length is 4 bytes, representing the range: ' 1970-01-01 00:00:01 '-' 2038-01-19 03:14:07 ', can be null, or you can customize the value. It is important to note that when this field is created, when you add "on Update Current_timestamp", the field is automatically changed to the current system time when it inserts a value.

3, int

This type of storage length is 4 bytes, which is typically used to store the Linux timestamp in the form of a storage precision of seconds. Indicates that the maximum is date (' y-m-d h:i:s ', 4294967295) up to 2106-02-07 14:28:15, can be null, but the business logic is designed to use a non-null value whenever possible. It has the advantage of numerical storage, space-saving. But the readability is poor. In addition, if the business often needs to arrange or query by time, try to use the int type.

4, bigint

This type of storage length is 8 bytes, from -2^63 (-9223372036854775808) to 2^63-1 (9223372036854775807) for integer data (all numbers). Ideal for business-accurate time storage with milliseconds of storage accuracy. Please refer to int for advantages and disadvantages.

In addition, the time storage in MySQL has the following types:

Year: The number of bytes is 1, and the value range is "1901--2155".

DATE, the number of bytes is 4, and the value range is "1000-01-01--9999-12-31".

Time, the number of bytes is 3, and the value range is " -838:59:59--838:59:59".

In the actual database modeling process, use less.

In conclusion, we believe that the storage of time needs to be based on specific business scenarios. Generally speaking, the type of int used more, on the one hand query efficiency is high, on the other hand can display time more flexible way, the general language provides conversion functions, such as well-known PHP in the date () function.

Those things about MySQL (2) How time type data is stored

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.