informix日期格式转换

求出2个日期之间的秒数(或天数，周数，月数等)是非常常见的需求，常用INFORMIX的人一定会遇到这样的问题：在INFORMIX里2个日期相减得到的却是一个奇怪的时间格式的东西(如：365 11:11:11.2231)，那么怎样才能得到2个日期之间的秒数差呢？在最后面我给出了对应的自定义函数(存储过程)，执行到数据库里当日期函数用就可以了。。当然在此之前，我会先来分析一下是怎样得到的需要的结果，有兴趣了解原理的的可以看看，没兴趣的可以跳过直接将存储过程拿走。。。

 

基本思路很简单，就是通过分析上面的那个奇怪的时间格式来分别取出2个日期之间的年，月，日，时，分，秒然后通过换算相加来得出总共为多少秒即是2个日期之间的秒差。。

首先我们拿一个简单的数据出来：

CREATE TABLE t_tab(
  t_date1 DATETIME YEAR TO SECOND,
  t_date2 DATETIME YEAR TO SECOND
);

INSERT INTO t_tab VALUES('2011-1-1 12:12:12','2010-1-1 00:00:00');

很简单的一个表，2个时间，一个是减数，一个是被减数：

select t_date1 - t_date2
from t_tab;

-----------------------

      365 12:12:12

我们看到得出的结果是一个奇怪的时间，这个时间明眼人通过我的2个日期数据看一下就会明白，先是2个日期相差的天数，后面则是标准的时分秒，这样结果就好理解了，INFORMIX把处理2个日期相减的结果分成了2个部分，将其相差的天数算出来作为前半部分，然后将富余出来的不到1天的时间处理为标准的00:00:00的格式放在后半部分，常查INFORMIX资料的人会看到过INFORMIX有一种时间类型叫interval的解释，就是说的这种格式的时间类型。

 

知道它的构成之后该怎么办？给大家介绍这样一条SQL的经验之谈：几乎任何类型的数据都可以被当做字符串来处理，而除类型转换以外的大多字符串处理都会用到“截取字符串”。根据这个经验，我们只要将这个时间类型转换为字符串，然后分别截取他的天数，小时数，分钟数，秒数，然后计算即可。。。

 

这个时候我们就要祭出一个常用的字符串处理函数substr()，我们来稍微温习一下这个函数的常用格式:substr(str,起始位置,偏移量)，其中参数1是你要截取的字符串或对应的字段。参数2是开始截取的起始位置，在informix中是取的字节数，如果是1就从第一个字节开始，3就从第三个字节开始，负数的话就从倒数第N个字节开始(PS:有些其他数据库如oracle取的则是字符数，即1就是第一个字符开始，区别在于中文或其他特殊字符可能1个字符会占用2到3个字节，在本章中日期都是由数字或冒号等单字节的字符组成，不用考虑这个问题)。然后第3个参数是从起始位置往后截取多少字节，如果是负数就反过来往前截取(注意并非是终止位置而是从起始位置往后截取的字节数，同样其他有些数据库如oracle是字符数)，substr函数常用的就这前3个参数，不同的数据库可能还会对其曾加一些参数。

 

啰嗦介绍了一下substr，下面就开始我们的截取工作，这里我们跳过一堆繁琐的实验过程只说一个很重要的结果，即：固定前9位是该时间字段的第一部分也就是天数，第10位是个空格，从第11位到第18位才是标准的00:00:00格式的时间字符串，如果精确度更高就继续往后顺眼比如00:00:00.2345，小数点后面的就是毫秒了。。结果如下：

 

select
  substr(t_date1 - t_date2,1,9) a,
  substr(t_date1 - t_date2,11,19) b
from t_tab

 

        a           b

---------------------------------
      365        12:12:12

 

稍微解释一下，很多人肯定会疑惑前面只有365天为什么会占9位？你们可以把这个时间格式的第一部分理解成右对齐，其中365占据第7，8，9位，而前面6位用空格填充了，从另一个角度看的话也就是，这个类型可以支持达到9位数的天数，相比这样做的初衷也有为了方便我们截取字符串的原因吧。。总之位数固定的话对咱们使用者来说不是坏事。。去掉天数的前6位的空格很容易，只要用to_number转成数字类型就可以了。

分别截取出了天数，小时，分，秒数后就是换算了，1分钟 =  60秒，1小时 =  60分钟 =  3600秒，1天 = 24小时 = (24*3600)秒，那么我们这个例子里2个日期相差多少时间呢？12秒 + (12分 * 60)秒 + (12小时 * 3600)秒 + (365天 * 24*3600)秒，相得的结果就是2天之间相差的秒数了。。。之所以换算到秒是因为从小粒度单位往大粒度单位计算更方便，如果你想知道2个日期间隔多少分，就除以60，间隔多少小时就除以3600依次类推，下面具体给出每个粒度的时间对应在改类型格式中的位置：

 

天数  →  字符串1~9位，第10位是空格

小时 →  字符串11~12位，第13位是冒号，下同

分钟 → 字符串14~15位

秒数 → 字符串17~18位

毫秒 → 字符串20位以后，第19位是小数点

 

原理都讲完了，下面就贴出我写好的存储过程，有需要的不用客气，直接拿走，转载的话也请多写一下我的地址：

 

CREATE PROCEDURE fn_dev_datetime(date1 datetime year to second,date2 datetime year to second)
returning integer;

DEFINE out_second INTEGER;

DEFINE t_day INTEGER;
DEFINE t_hour INTEGER;
DEFINE t_minute INTEGER;
DEFINE t_second INTEGER;

LET t_day = to_number(substr(date1 - date2,1,9));
LET t_hour = to_number(substr(date1 - date2,11,2));
LET t_minute = to_number(substr(date1 - date2,14,2));
LET t_second = to_number(substr(date1 - date2,17,2));

LET out_second = t_day*24*3600 + t_hour*3600 + t_minute*60 + t_second;

RETURN out_second;
end PROCEDURE;