Date time type in database

Preface :

Hello everyone , Here's a little ! today , A senior student in the lab found that there was a problem with the code I submitted once ! I almost thought I was going to be specially cared for , All right, let's get down to business !

See the time and date type in the database , Many people will say ha , Direct save string String The type ! Simple point ~emm

I thought so at first , Then it was rejected !

String storage date

Store the date and time as a string , Then split the date and time into two fields , A field records the date （ Such as yyyy-MM-dd）, Another field stores the time （ Such as ：HH:mm:ss） In the form of . I won't say much in this way , Unless there are very few other scenes , Not recommended . When processing in this way, the date , Not only is there a performance problem , Compare 、 Handle 、 Taking the range and so on are all troublesome things . The reason for this approach , It's also a reminder , If your database date field is still stored in a string , It needs careful consideration .

such as :

 Front end presentation date   I suppose it is now yyyyMMdd hh:mm:ss
 The front end is now changed to MMddyyyy  So what should we do ?
 What should I do when I encounter the problem of time zone ?

Chiaus If you just save a string ! It's hard to change !

So the following string will be used :

DateTime type

DateTime Type stores values of both date and time . The format we intuitively see is ：yyyy-MM-dd HH:mm:ss. The time range it supports is “1000-00-00 00:00:00” To “9999-12-31 23:59:59”.
but DateTime Time zone information is not stored in , Only local time is stored . in other words ： If you will The server Modify the time zone of , The date and time recorded in the database will not change accordingly .
that , The read data is inconsistent with the newly stored data , It can also be said that it is wrong .
Usually , In this case , If cross time zone issues are involved , Consider using a separate field to store the time zone .

Sometimes you use :

Timestamp type

Timestamp type ： It is also data with both date and time . The format of storage and display is the same as Datetime equally . The supported time range is “1970-01-01 00:00:01” To “2038-01-19 03:14:07”.
Timestamp Type not only stores date and time , Time zone information is also stored . If the Timestamp Type storage , The implementation of each database will be different , Some have carried out automatic conversion of internal time zone .
If the time zone of the application server is inconsistent with that of the database server , You're not sure if the database driver will automatically help you convert .
meanwhile , The time frame is Timestamp Indifference to .

Unix timestamp

Baidu Encyclopedia

Due to the time zone problem , People in different parts of the earth see the sun rise at different times . For example, there is a time difference between Europe and Beijing 6-7 Hours , When in the morning 8 When I see the sun in Beijing , Europe is still in the early morning 1-2 spot .

Except as mentioned above Timestamp The type store contains dates and times outside the time zone , You can also use “ Absolute time ” To calculate , The unit is in seconds .

In the computer , The current time is from a base time （1970-1-1 00:00:00 +0:00） The number of seconds to now , Use an integer to represent .

There is a very magical thing , Namely 2038 In the problem

In order to investigate the setting of date and time in the database , Ali's development manual is also a reference , It is puzzling that Ali used datetime type .

That's true , After all, the business scope of each company is different , Different use scenarios . Excellent ideas can be referred to , But it can't be copied . Just like this article , Different types of date and time storage are introduced , According to your business scenario, the most suitable one is the best . I finally decided to use absolute timestamp for processing .