最近用到的informix sql，关于时间函数

http://lhbthanks.iteye.com/blog/1667174
http://blog.csdn.net/amaryh/article/details/5282634

http://www.forumtopics.com/busobj/viewtopic.php?p=507955&sid=ecc0559a028f637dcd6459a91a3fbbed

两个时间段函数相减，得到的秒数。 extend(sendtime,hour to hour) 截取小时数
select sendtime, sheetcompletelimit, ((sheetcompletelimit-sendtime)::interval second(9) to second ::char(20) ::integer), extend(sendtime,hour to hour)
from businesschange_main where sendtime > '2013-07-12 15:59:59'
2013-7-12 下午4:17:25'2013-7-19 下午3:50:01'603156
2013-7-12 下午4:28:25'2013-7-19 下午4:01:01'603156


inforix统计sql，case when 语句，sum求和。
select task.subtaskflag as s1,
nvl(sum(case when task.taskstatus='2' then 1 else 0 end),0) as f1,--"待受理"，指派发后未受理的工单
nvl(sum(case when task.taskstatus='8' then 1 else 0 end),0) as f2, ---"待处理"，指受理后未回复的工单
nvl(sum(case when task.taskname='HoldHumTask' then 1 else 0 end),0) as f3,---"待归档"，指回复后未归档的工单
nvl(sum(case when main.sheetcompletelimit-current>=interval(2) day to day and main.sheetcompletelimit-current<=interval(3) day to day  then 1 else 0 end),0) as f4,--"3天超时预警"，指距回复时限还有3～5天的工单
nvl(sum(case when main.sheetcompletelimit-current>=interval(3) day to day and main.sheetcompletelimit-current<=interval(5) day to day then 1 else 0 end),0) as f8,--"5天超时预警"，指距回复时限还有2～3天的工单
nvl(sum(case when main.sheetcompletelimit-current>=interval(1) day to day and main.sheetcompletelimit-current<=interval(2) day to day then 1 else 0 end),0) as f5,--"2天超时警告"，指距回复时限还有1～2天的工单
nvl(sum(case when  main.sheetcompletelimit>=current and main.sheetcompletelimit-current<=interval(1) day to day then 1 else 0 end),0) as f6,--、"1天超时警告"，指距回复时限还有0～1天的工单
nvl(sum(case when current>main.sheetcompletelimit then 1 else 0 end),0) as f7  --"已超时工单数"，指已超时未回复的工单。
 from businessdredge_main main,businessdredge_task task where 
 main.sendtime>=extend(today-30,year to second) 
and main.id=task.sheetkey and (task.taskstatus='2'or task.taskstatus='8'or task.taskname='HoldHumTask') 
and task.createtime in (select max(createtime) from businessdredge_task kk where kk.sheetkey=main.id group by main.id) group by task.subtaskflag