Wechat official account “Backend Advanced” focuses on back-end technology sharing: Java, Golang, WEB framework, distributed middleware, service governance and so on. The old driver give each other all the way, take you to advance, too late to explain quickly get on the car!

I noticed that some dates in the database are stored in strings? Therefore, I discussed with some friends about how to save the date of the database. In fact, I always suggested using numerical value to save the timestamp directly. Why did I suggest this?

Here, I’ll explain why numerical timestamps are the best solution, from the concept of time zones, and to share the details so that more developers are aware of them.

It is believed that time zones will be familiar to many people because the earth is round and the sun rises at a different Angle in different corners of the globe, meaning that everyone shows the time differently.

Here’s an example:

It is 10 o ‘clock in Beijing time when we are in east zone 8, so it is 3 o ‘clock when we are in east Zone 1, but their time is equivalent:

"The 2019-06-20 10:00 + 8" = "The 2019-06-20" 3:00 + 1 p.m.
Copy the code

So how do you save the time in data when the time is displayed differently for people in different time zones?

I’m going to assume that you are using the new Date() method to save the current Date, but as far as I know, the DateTime type of the database does not have the time zone information. If you save the Date in DateTime format, you will lose the time zone information. The date data read from the database is wrong!

You might say, well, IF I save it with timeStamp, I won’t lose the time zone information. It’s not missing. It’s all right. But as far as I know, timestamps can’t last longer than 2037, and you have to consider that each data may have a different timeStamp type.

As for storing time in strings, it is even less recommended, and it is also a problem to compare dates, let me give you an example:

to_char(SYSDATE, 'yyyy-MM-dd') > START_TIME
Copy the code

To compare a time size, I need to do that, and I need to convert the system time to a string for you to compare, and when I convert it to a string comparison, the database internally converts it to a time to compare, how much better do you think that would be?

We also know that there are plenty of time zone conversions available in the new TIME API LocalDateTime in JDK8, but even if you claim to be proficient in all the fancy uses of LocalDateTime, you’ll have to deal with the cumbersome conversions.

Therefore, we need an “absolute time” to help us record the date and save the time for conversion. This “absolute time” is the timestamp. The timestamp is defined as starting from a base time, which is “1970-1-1 00:00:00 +0:00”. Measured in seconds, the time integer increases as time passes. In this way, I just need a number, you can perfectly show the time, and this value is an absolute value, namely no matter in every corner of the earth, the said the timestamp of the time, are the same, the generated values are the same, and there is no concept of time zones, so in the system of the time of transmission, do not need additional transformation, The local time is converted to string format only when displayed to the user.

It is also important to note that existing programming languages provide a way to retrieve timestamps, which is not too convenient for our projects to interact with each other in different languages. So I strongly recommend that all time interactions on the front and back end be timestamp interactions.

At this time, there may be a classmate again to bar wave, you use a numerical to represent time, when I check the database, in my eyes and oral, don’t know what is the time, I think this don’t have to worry about, you need to check database is nothing more than to see the data, you face the timestamp in SQL a conversion function of time, such as:

from_unixtime(1561053690000)
Copy the code

If you’re going to go ahead and say I just want to see the time in the database table, I think if you want to do that, why do you need a front end, just take the current side of the database and show it.

Let me summarize the many benefits of storing timestamps numerically in databases:

  1. Date comparison in the database is not too convenient, the first grade of primary school will be math problems, and good performance;
  2. Numbers are no barrier to any system interaction;
  3. Absolute time based numerical storage, there is no time zone problem;
  4. In the interaction process, abandon unnecessary heavy conversion, a number to go all over the world, the user needs to display, the front end only need to get the time stamp to display the correct local time;
  5. Solve the problem caused by the time implementation of each database is not the same, such as Mysql time function and Oracle will have some differences, if your current SQL time function, change the database is likely to error.