Differences and pros and cons of timestamp,datetime,int types in Golang MySQL

Source: Internet
Author: User
This is a creation in Article, where the information may have evolved or changed.


Golang time:https://golang.org/pkg/time/


Golang in time package usage http://blog.csdn.net/chenbaoke/article/details/41519193



First, the Datetim and timestamp types occupy a different storage space, the former 8 bytes, the latter 4 bytes, the result is that the two can represent different time range. The former range is 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59, the latter range from 1970-01-01 08:00:01 to 2038-01-19 11:14:07. So you can see that the range of timestamp support is smaller than the datatime, and is prone to exceeding the situation.

Second, TIMESTAMP type by default, the TIMESTAMP column is automatically populated/updated with the current time (CURRENT_TIMESTAMP) when the INSERT, update data.

Third, timestamp is affected by time zone timezone and the MySQL version and SQL mode of the server

So generally speaking, I prefer to choose datetime, as you say about the index problem, choose DateTime as the index, if you encounter a large number of data query slow situation, you can also partition table resolution.


In fact, the difference in speed is not very big, you can do your own testing to know. Internal storage is integers, except that datetime and timestamp are displayed only when they are displayed as people can read the date (of course, the storage space is a bit different, shaping and timestamp are 4 bytes, datetime is 8 bytes), indexing should be no different, This is not sure, if please correct me ...

In addition, datetime and timestamp have a small advantage over int, that is, for a time type, a series of time functions can be used

Q:MySQL Time field seems to have a variety of options, like the general situation (I also) is usedINTtype to indicate that the timestamp is stored directly. But I know there's at least one in MySQL.TIMESTAMPand theDATETIMEtype can be used to store time, I do not know what the three are different in use, how to think about the use of the scene, especially if they are indexed or query speed difference?


These types of choices still look at your needs.

I use timestamp more, for logging what needs, timestamp absolutely enough, unless you guarantee that your program can be used until 2038, even if you can use the Migration program processing ...

If the requirement is to allow the user to save more time than timestamp can save (@QingchaoWu has given the scope of timestamp) such as Todo list or something that allows users to plan things beyond 38, then use DateTime.



##################################


Reprint please specify from Souldak, Weibo: @evagle
Following content Integrated FilteringFrom the Internet:

Int
1. Occupy 4 bytes
2. After indexing, query speed is fast
3. Conditional range Search can be used using between
4. You cannot use the time function provided by MySQL
conclusion: Suitable for data tables that require large time-range queries

Datetime
1. Occupy 8 bytes
2. Allow null values, you can customize the value, the system does not automatically modify its value.

3. Actual format storage (Just stores what are you having stored and retrieves the same thing which you have stored.)

4. Time zone Independent (It has nothing to deal with the TIMEZONE and Conversion.)

5. You cannot set a default value, so you must manually specify the value of the DateTime field to successfully insert the data in cases where null values are not allowed. 6. You can use the now () variable to automatically insert the current time of the system when specifying the value of a DateTime field. Conclusion: DateTime types are appropriate for recording the original creation time of the data, because no matter how you change the value of the other fields in the record, the value of the DateTime field does not change unless you manually change it.
Timestamp
1. Occupy 4 bytes
2. Allow null values, but not custom values, so null is meaningless.
3. The timestamp value cannot be earlier than 1970 or later than 2037. This indicates that a date, such as ' 1968-01-01 ', is valid for a datetime or date value but is not valid for the timestamp value, and is converted to 0 if assigned to such an object.

4. Values are saved in UTC (It stores the number of milliseconds)

5. Time zone conversion, the current time zone is converted when stored, and then converted back to the current time zone.

6. The default value is Current_timestamp (), which is actually the current system time. 7. The database automatically modifies its value, so you do not need to specify the name of the timestamp field and the value of the timestamp field when inserting a record, you only need to add a timestamp field when you design the table, and the value of the field will automatically become the current system time when you insert it. 8. The timestamp value of the corresponding record is automatically updated to the current system time when the records in the table are modified at any later time. Conclusion: The timestamp type is suitable for recording the last modification time of the data, because the value of the timestamp field is automatically updated as soon as you change the value of the other fields in the record.

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.