当前位置:网站首页>Date time type in database
Date time type in database
2022-04-23 12:52:00 【Small due to interest】
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 .
TIMESTAMP(14) | YYYYMMDDHHMMSS |
TIMESTAMP(12) | YYMMDDHHMMSS |
TIMESTAMP(10) | YYMMDDHHMM |
TIMESTAMP(8) | YYYYMMDD |
TIMESTAMP(6) | YYMMDD |
TIMESTAMP(4) | YYMM |
TIMESTAMP(2) | YY |
Unix timestamp
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 .
版权声明
本文为[Small due to interest]所创,转载请带上原文链接,感谢
https://yzsam.com/2022/04/202204231246456957.html
边栏推荐
- Image attribute of input: type attribute of fashion cloud learning -h5
- 风尚云网学习-h5的input:type属性的image属性
- 22. 括号生成
- CVPR 2022&NTIRE 2022|首个用于高光谱图像重建的 Transformer
- 解锁OpenHarmony技术日!年度盛会,即将揭幕!
- Kubernets Getting started tutoriel
- I changed to a programmer at the age of 31. Now I'm 34. Let me talk about my experience and some feelings
- [wechat applet] Z-index is invalid
- 21 days learning mongodb notes
- box-sizing
猜你喜欢
Softbank vision fund entered the Web3 security industry and led a new round of investment of US $60 million in certik
网站首页文件被攻击篡改的形式有哪些
CVPR 2022&NTIRE 2022|首个用于高光谱图像重建的 Transformer
Realize several "Postures" in which a box is horizontally and vertically centered in the parent box
免费试用一个月的服务器,并附上教程
Packet capturing and sorting -- TCP protocol [8]
STM32 project transplantation: transplantation between chip projects of different models: Ze to C8
将新增和编辑的数据同步更新到列表
SSM framework series - annotation development day2-2
SSM framework series - JUnit unit test optimization day2-3
随机推荐
[Blue Bridge Cup] April 17 provincial competition brushing training (the first three questions)
Web17 -- use of El and JSTL
A graphic designer's fantasy world | ones characters
uni-app 原生APP-本地打包集成极光推送(JG-JPUSH)详细教程
Fashion cloud learning - input attribute summary
QT redraw events and cuts
【vulnhub靶场】-dc2
box-sizing
BUUCTF WEB [BJDCTF2020]The mystery of ip
风尚云网学习-input属性总结
解决disagrees about version of symbol device_create
基于卷积神经网络的遥感影像分类识别系统
How do traditional enterprises cope with digital transformation? These books give you the answer
BUUCTF WEB [BUUCTF 2018]Online Tool
软件测试周刊(第68期):解决棘手问题的最上乘方法是:静观其变,顺水推舟。
有趣的IDEA插件推荐,给你的开发工作增添色彩
22. 括号生成
QT draw image
leetcode:437. 路径总和 III【dfs 选还是不选?】
云原生KubeSphere部署Redis