面试官数据库日期类型字段，需要兼容不同数据库，应如何选择？

　　作者：sp42a 来源：httpsp://zhangxin.blog.csdn.net/article/details/124955363
　　当设计一个产品，其中很多地方要把日期类型保存到数据库中，如果产品有兼容不同数据库产品的需求，那么，应当怎样设计呢？
　　当然，首先想到的是，使用数据库的 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 维护  到什么时候，即便没有看到数据库设计文档，都能看明白并准确理解数据库中一条信息中，这个字段保存到确切信息
　　使用这种方式的特点是牺牲一点效率，满足了查询结果的直观性和准确性要求。
　　总结一下，字段类型的选择，还是根据场景的需要来选择，从功能、效率要求、持续开发的要求、维护的要求几个方面综合考虑。