浅谈数据库日期类型字段设计应该如何选择
作者:sp42a 发布时间:2024-01-21 13:55:21
当设计一个产品,其中很多地方要把日期类型保存到数据库中,如果产品有兼容不同数据库产品的需求,那么,应当怎样设计呢?
当然,首先想到的是,使用数据库的 Date 或 DateTime 类型,可是看看不同数据库这些类型间的区别吧,真让人望而止步。Mysql 数据库:它们分别是 date、datetime、time、timestamp 和 year。
date :“yyyy-mm-dd”格式表示的日期值
time :“hh:mm:ss”格式表示的时间值
datetime: “yyyy-mm-dd hh:mm:ss”格式
timestamp: “yyyymmddhhmmss”格式表示的时间戳值
year: “yyyy”格式的年份值。
范围:
date “1000-01-01” 到 “9999-12-31” 3字节
time “-838:59:59” 到 “838:59:59” 3字节
datetime “1000-01-01 00:00:00” 到 “9999-12-31 23:59:59” 8字节
timestamp 19700101000000 到 2037 年的某个时刻 4字节
year 1901 到 2155 1 字节
Oracle 数据库:
Date 类型的内部编码为12
长度:占用7个字节
数据存储的每一位到第七位分别为:世纪,年,月,日,时,分,秒
TIMESTAMP是支持小数秒和时区的日期/时间类型。对秒的精确度更高
TIMESTAMP WITH TIME ZONE 类型是 TIMESTAMP 的子类型,增加了时区支持,占用13字节的存储空间,最后两位用于保存时区信息
INTERVAL 用于表示一段时间或一个时间间隔的方法。在前面有多次提过。INTERVAL有两种类型.
YEAR TO MONTH 能存储年或月指定的一个时间段.
DATE TO SECOND 存储天,小时,分钟,秒指定的时间段.
sql server:datetime 和 smalldatetime
datetime数据类型所占用的存储空间为8个字节,其中前4个字节用于存储1900年1月1日以前或以后的天数,数值分正负,正数表示在此日期之后的日期,负数表示在此日期之前的日期;后4个字节用于存储从此日零时起所指定的时间经过的毫秒数。
smalldatetime数据类型使用4个字节存储数据。其中前2个字节存储从基础日期1900年1月1日以来的天数,后两个字节存储此日零时起所指定的时间经过的分钟数。
smalldatetime数据类型与datetime数据类型相似,但其日期时间范围较小,从1900年1月1日到2079年6月6日。此数据类型精度较低,只能精确到分钟,其分钟个位为根据秒数四舍五入的值,即以30秒为界四舍五入。
如果没有兼容多种数据库这个要求,我会毫不犹豫的使用数据库的 Date 类型。因为如果使用 Java 框架产生代码,对数据库中定义为 Date 类型的字段,甚至能在页面上产生出JS的时间选择框,的确能节省很多开发时间。而兼容不同数据库,就希望产品在由一种数据库,迁移到另外一种数据库时,尽可能小的代价,使用了 Date,看来就很困难了。
有一个疑问,不知道目前流行的ORM对这个处理得是不是好?因为工作不怎么涉及这方面,所以不大了解。
在之前的设计开发中,因为有支持多种数据库这种需求,所以首先否定了日期时间这样的类型。
曾经使用过毫秒数(Java 的 System.currentTimeMillis())这种方式,但是选用这个方式,考虑的不是使用起来是否方便或者数据迁移,而是考虑到下面的原因:
Java 取到的毫秒数是对时间点的一种准确描述。定义如下:java.lang.System.currentTimeMillis(),它返回从 UTC 1970 年 1 月 1 日午夜开始经过的毫秒数。
我们可以看到,这个定义,保证了这个时间值能够被后续设计开发的人员正确和准确的理解,能够为所有的应用正确理解,能够在所有时区上正确反映为正常的时间形式。
当时的产品设计是有海外客户的,所以当时的设计,在数据库里保存的,应该是一个“准确的时间”。例如“20120926080000”实际上并没有严格的表示出时间,因为北京时间2012年9月26日8点和格林威治时间2012年9月26日8点显然是不一样的。
虽然我们都是在一个确切的时区里,例如中国都是使用东八区时间,但是需要考虑的是:
有些产品是可能有海外客户的
产品所运行的机器,时区的设置未必都是东八区。
在这种情况下,如果数据库里的时间不准确,会给程序运行带来问题。这种方式最大的缺点在于:
不方便对时间进行分组查询,比如按月统计、按季 统计
DBA在维护时,不能直观的根据返回的行结果,看到简单明了的结果(看到的是毫秒数)
使用这种方式的特点是牺牲一点易用性和可理解性(不易于维护和理解),满足了查询结果的直观性和准确性要求,同时最大限度考虑运行效率。为了解决这个问题,我设计了一个辅助的措施,就是建立一个数据库函数来进行时间转换,把毫秒数的时间转为制定时区和格式的时间串,DBA 在维护时可以使用。测试了 Oracle 和 DB2 上,都可以这样。例如之前的查询的时候为:
SELECT username,user_addtime from userinfo
这个查询显示的是毫秒数,使用内置函数后写成:
SELECT username,date2str(user_addtime) from userinfo
这样返回的就是东八区、预先定义好格式的字符串了。
在之后的设计里,还使用过 YYYYMMDDHHmmSST 格式,其中的“T”指时区,加入时区,带来的影响有:
日期时间字段就不能在使用数值来存储了,字符串比数字存储和检索的效率都要低。
应用程序需要加上额外的处理
带来的好处是:
便于 DBA 维护
到什么时候,即便没有看到数据库设计文档,都能看明白并准确理解数据库中一条信息中,这个字段保存到确切信息
使用这种方式的特点是牺牲一点效率,满足了查询结果的直观性和准确性要求。
总结一下,字段类型的选择,还是根据场景的需要来选择,从功能、效率要求、持续开发的要求、维护的要求几个方面综合考虑。
来源:https://zhangxin.blog.csdn.net/article/details/124955363
猜你喜欢
- 数据字典是Oracle存放有关数据库信息的地方,其用途是用来描述数据的。比如一个表的创建者信息,创建时间信息,所属表空间信息,用户访问权限信
- 正在看的ORACLE教程是:Oracle RMAN快速入门指南。前言: 这篇文章主要介绍RMAN的常用方法,其中包含了作者一些自己的经验,里
- 为什么要用flash呢?动画流畅,视觉效果好缓存能力强那使用flash有什么问题呢?需要Flash播放器在M$的补丁打遍天下之前IE有那神奇
- 在Matplotlib实际使用中会有生成不同大小subplots的需求。import numpy as np import matplotl
- 前言这是个在写计算机网络课设的时候碰到的问题,卡了我一天,所以总结一下。其实在之前就有用requests写过python爬虫,但是计算机网络
- 在一些情况下,MySQL可以直接使用索引来满足一个 ORDER BY 或 GROUP BY 子句而无需做额外的排序。尽管 ORDER BY
- python作为脚本性语言,加上它的简便易用性。会经常当作脚本用来处理一下数据和格式。其中处理文件就是频繁用处之一。简单编写几个常用的xls
- 前言:在fastapi中,我们定义的查询参数是可以设置成:必选参数 or 可选参数。可选查询参数只要给查询参数的默认值设置为None,表示该
- 在制作网页的过程中,自适应问题是经常遇到,而又最让人头疼的,看了好多种解决的方法,但是都不是万能的。今天遇到这个问题,实在让我挠头,借鉴了
- 通常的聊天室所采用的程序,也就是Chat程序了,其基本结构原理是不会采用到数据库的。那究竟采用什么技术呢?我们知道ASP变量当中Sessio
- 本文实例讲述了MySQL截取和拆分字符串函数用法。分享给大家供大家参考,具体如下:首先说截取字符串函数:SUBSTRING(commenti
- Python自动化测试 Eclipse+Pydev 搭建开发环境C#之所以容易让人感兴趣,是因为安装完Visual Studio, 就可以很
- 需要画框取消注释rectangleimport cv2import os,sys,shutilimport numpy as np# Ope
- 前言在进行接口测试时,有些接口字段在不需要测试的时候往往是被写死的,但是你不能保证它就不会出现问题,所以在平时测试的时候就需要覆盖各种情况,
- mysql5.7.18zip版本在windows的安装,就是解压,初始化,然后做一些密码修改的设置即可使用,如果需要远程连接,需要更改用户表
- Variant变量一般会将其代表的数据子类型自动转换成合适的数据类型,但有时候,自动转换也会造成一些数据类型不匹配的错误.这时,可使用转换函
- 需求:从一台Oracle数据库获取数据,本以为是很简单的事情,直接将原来的SqlClient换成OracleClient调用,结果远没自己想
- 在安装SQL Server 2000 ,安装进程自动地为“BUILTIN\Administrators&rdqu
- 本文实例讲述了mysql中的sql_mode模式。分享给大家供大家参考,具体如下:mysql数据库的中有一个环境变量sql_mode,定义了
- 1、上下文管理的使用场景凡是要在代码块前后插入代码的场景,这点和装饰器类似。资源管理类:申请和回收,包括打开文件、网络连接、数据库连接等;权