“This is the sixth day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021.”

Hi, everyone, I am three days fishing, two days drying net small 66

preface

Welcome to our GitHub repository Star: github.com/bin39232820… The best time to plant a tree was ten years ago, followed by now

I had some bugs due to the time zone before, and then I found that MY knowledge of this area was also weak, so I systematically studied it together with everyone.

UTC time

Coordinated Universal Time (UTC) is also known as Universal Time, Universal Standard Time, and Coordinated International Time. Because of the different abbreviations for CUT and TUC, the compromise was called UTC. Coordinated Universal time (UTC) is a time measurement system that approximates universal time as closely as possible in time, based on the length of atomic time seconds. In Mainland China, ISO 8601-1988 date and Time Representation for Information Exchange in Data Elements and Exchange Formats (GB/T 7408-1994) is adopted as the International Coordinated Time instead of GB/T 7408-1994. Taiwan of China adopts CNS 7648 “Data elements and Interchange Format – Information Interchange – Date and Time Representation”, which is called universal unified time.

The time zone

The earth’s rotation causes the sun to rise in the east and set in the west, with day at sunrise and night at sunset. Diurnal alternation is used throughout the world to determine date changes and time progression.

However, different time standards in different locations caused time confusion. To overcome this situation, in 1884, the international Longitude Conference was held in Washington, D.C. The countries at the conference established a common standard time system, with 180° longitude as the theoretical limit of the day, with longitude intervals of 15° and local time varying by one hour.

Although China spans multiple time zones, only one standard time zone is adopted, which is also the result of long-term practice. The fundamental reason for the difference of time zone system between China and America lies in the difference of population distribution. Constrained by geography and economic development, the largest cities and population centers of the United States are located in the central and east and West coasts. China’s population distribution is relatively concentrated, the vast majority in the “Heihe Tengchong line” east.

Now we talk about the “Beijing time”, is not the real sense of Beijing time, but to the Time center of Pucheng County in Weinan City of Shaanxi Province issued by the east eight district time (120 degrees east longitude) as a unified name of the “Beijing time”.

The time stamp

“The timestamp is the total number of seconds of Greenwich Mean Time from January 1, 1970 (00:00:00 GMT) to the current time. In layman’s terms, a timestamp is a complete, verifiable piece of data that indicates that a piece of data existed at a particular point in time.”

In computers, “timestamp” generally refers to a Unix timestamp, which is the number of seconds since the Unix era (00:00:00 GMT, January 1, 1970) to the current time.

A timestamp is a time that can be converted to any time zone

How does the database store time

String store date

We must be in the beginning of the contact development, this approach is desirable, simple, easy to use, high identification, a look to understand

  • Strings take up more space
  • Date comparisons stored in strings are inefficient (character by character) and cannot be computed using date-related apis.
  • Time zone issues You can’t store time zones with strings, there are a lot of problems with overseas projects, so for some projects it is very inappropriate to store it

Datetime and Timestamp

Datetime and Timestamp are two similar data types provided by MySQL. How should they choose between them?

The preferred TimeStamp.

Datetime is more like a combination of the time on your calendar and the time on your watch, meaning a specific time.

Timestamp is more suitable to record the time, for example, my time in the East 8th district is now 2016-08-02 10:35:52, you are in Japan (the east 9th District at this time is 2016-08-02 11:35:52), I am chatting with you, the database recorded the time, after taking it out, The time is 2016-08-02 10:35:52 for me, and 2016-08-02 11:35:52 for you in Japan. So you don’t have to worry about time zone calculations.

The time range is timestamp (1970-2038), of course datetime (1000-9999) does not record when Liu Bei was born (161 years).

DateTime No time zone information (time zone independent) DateTime saves the time corresponding to the time zone of the current session. What’s the problem with that? When you change your time zone, such as when you change the server address or change the client connection time zone Settings, it will cause you to read the wrong time from the database. Don’t take this problem lightly, many systems make a lot of jokes because of this problem.

Are numeric timestamps a better choice

A lot of times, we’ll also use a value of type int or BigInt which is a timestamp.

This storage mode has some advantages of Timestamp type, and use it for date sorting and comparison operation efficiency will be higher, cross system is also very convenient, after all, only store the value. The downside is that the readability of the data is so poor that you can’t see the time directly.

conclusion

Timestamp is recommended because the numerical representation of time is not intuitive

Each approach has its own advantages and is based on the actual situation. Here is a simple comparison of these three methods, so that you can choose the correct data type of storage time in actual development:

Well today’s share to this, I am small 66, three days fishing, two days drying nets.