当前位置:网站首页>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
边栏推荐
- Can I take the CPDA data analyst certificate for 0 foundation
- AD20补充笔记3—快捷键+持续更新
- 0基础可以考CPDA数据分析师证书吗
- STM32工程移植:不同型号芯片工程之间的移植:ZE到C8
- SSM框架系列——注解开发day2-2
- Recommended website for drawing result map
- 8 websites that should be known for product development to enhance work experience
- 进程虚拟地址空间区域划分
- 21 days learning mongodb notes
- Jiachen chapter Genesis "inner universe" joint Edition
猜你喜欢
【蓝桥杯】4月17日省赛刷题训练(前3道题)
CGC: contractual graph clustering for community detection and tracking
Pre competition practice of TIANTI competition
QT double buffer drawing
SSM framework series - JUnit unit test optimization day2-3
NPDP | how can product managers not be excluded by programmers?
航芯技术分享 | ACM32 MCU安全特性概述
I changed to a programmer at the age of 31. Now I'm 34. Let me talk about my experience and some feelings
31. 下一个排列
使用Source Insight查看编辑源代码
随机推荐
XinChaCha Trust SSL Organization Validated
SSM框架系列——Junit单元测试优化day2-3
STM32工程移植:不同型号芯片工程之间的移植:ZE到C8
BaseRecyclerViewAdapterHelper 实现下拉刷新和上拉加载
AD20补充笔记3—快捷键+持续更新
A graphic designer's fantasy world | ones characters
风尚云网学习-input属性总结
uni-app 原生APP-云打包集成极光推送(JG-JPUSH)详细教程
Ad20 supplementary note 3 - shortcut key + continuous update
What are the forms of attack and tampering on the home page of the website
leetcode-791. Custom string sorting
Zigbee之CC2530最小系统及寄存器配置(1)
How to prevent the website from being hacked and tampered with
[daily question] chessboard question
C#,二维贝塞尔拟合曲线(Bézier Curve)参数点的计算代码
mysql中 innoDB执行过程分析
BUUCTF WEB [BJDCTF2020]ZJCTF,不过如此
QT interprocess communication
Number of nodes of complete binary tree
Packet capturing and sorting -- TCP protocol [8]