浅谈数据库日期类型字段设计应该如何选择_MsSql

来源:脚本之家  责任编辑:小易  

当设计一个产品,其中很多地方要把日期类型保存到数据库中,如果产品有兼容不同数据库产品的需求,那么,应当怎样设计呢?

当然,首先想到的是,使用数据库的 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 维护到什么时候,即便没有看到数据库设计文档,都能看明白并准确理解数据库中一条信息中,这个字段保存到确切信息

使用这种方式的特点是牺牲一点效率,满足了查询结果的直观性和准确性要求。

总结一下,字段类型的选择,还是根据场景的需要来选择,从功能、效率要求、持续开发的要求、维护的要求几个方面综合考虑。

到此这篇关于浅谈数据库日期类型字段设计应该如何选择的文章就介绍到这了,更多相关数据库日期类型字段设计内容请搜索真格学网以前的文章或继续浏览下面的相关文章希望大家以后多多支持真格学网!

您可能感兴趣的文章:浅谈MySQL数据库中日期中包含零值的问题数据库 日期加减处理数据库的日期格式转换

  • 本文相关:
  • 在sql查询中使用表变量
  • mssql 数据库表行转列,列转行终极方案
  • 基于sqlserver的四种分页方式总结
  • mysql 及 sql 注入与防范方法
  • sql server数据库高可用日志传送的方法
  • sql server中常用的6个自定义函数分享
  • sql年龄计算的两种方法实例
  • sql优化技巧指南
  • ddl、dml和dcl的区别与理解
  • sql server时间戳功能与用法详解
  • SQL数据库的数据类型有哪些,请详细解释一下?
  • access数据库里的字段为日期类型,导出文本自动带时间,如何才...
  • 如何在sql server中把数据库中的日期字段转换为短日期格式
  • 求数据库字段类型设计规范
  • 设计access数据库,有学生信息等字段,另外每个一个学生还有对...
  • 如何设计数据库表中同一字段数据的一致性
  • vfp 设计数据库表的字段时,下面显示组框的“格式”是什么意思...
  • 如何为Access数据库表添加日期或时间戳?
  • 如何用sql语句获取日期大于当前日期的数据库数据?
  • VFP数据库表如何设置字符型字段只能输入数字字符?
  • “出生日期”字段,其值不能在未来的表达式该怎样设
  • 数据库物理设计中 类型 宽度 是否为主键 是什么意思呢。求专家...
  • vb2010如何对access数据库进行操作(添加新字段,新记录,修改...
  • java中怎么像数据库中添加输入的日期
  • 分析手机的属性及属性类型,怎么设计数据库vfp
  • 网站首页网页制作脚本下载服务器操作系统网站运营平面设计媒体动画电脑基础硬件教程网络安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess数据库文摘数据库其它首页浅谈mysql数据库中日期中包含零值的问题数据库 日期加减处理数据库的日期格式转换在sql查询中使用表变量mssql 数据库表行转列,列转行终极方案基于sqlserver的四种分页方式总结mysql 及 sql 注入与防范方法sql server数据库高可用日志传送的方法sql server中常用的6个自定义函数分享sql年龄计算的两种方法实例sql优化技巧指南ddl、dml和dcl的区别与理解sql server时间戳功能与用法详解sql server 2012 安装图解教程(附sql201sql server数据库入门学习总结microsoft sql server 2012 数据库安win7系统安装sqlserver2000的详细步骤(图文)sql重复记录查询的几种方法sqlserver中distinct的用法(不重复的记录)ddl、dml和dcl的区别与理解sql server错误代码大全及解释(留着备用)sql-order by 多字段排序(升序、降序)sql将一个表中的数据插入到另一个表中的方法sql select语句完整的执行顺序2分法分页存储过程脚本实例深入分析sqlserver查询计划sql server sa权限总结经典技术sql字符串函数大全和使用方法示例解决sql server虚拟内存不足情况sql 实用语句sql server误区30日谈 第3天 即时文件初始化特性可以在sql s一条sql语句搞定sql2000 分页sqlserver2019配置端口号的实现
    免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved