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;

©️2020 CSDN 皮肤主题: 大白 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值