informix常用时间运算和操作方法

本文转载于http://blog.itpub.net/70612/viewspace-1027846/

informix数据库中的时间运算

select first 1 today,current,current -1 units year,current -1 units day,current -1 units hour,current -1 units minute,current -1 units second from systables
select first 1 extend(current,year to day),extend(current,year to second),extend(current,year to year),extend(current,hour to second) from systables
select first 1 extend(current,year to day), extend(current,year to day) - 1 units day,extend(current,year to second),extend(current,year to year),extend(current,hour to second) from systables
select first 1 to_char(current,'%Y%m%d') from systables --Y大写和小写有区别，Y大写年份用4位数字，y小写年份用2位数字
select first 1 extend(to_date('20091015','%Y%m%d'),year to day) from systables
select first 1 day(current) from systables --直接取天
select first 1 date(current) from systables --取到天
select first 1 date(current) - interval(2) day to day from systables --当前日期减2天
select first 1 date(current) - interval(2) year to year from systables--当前日期减2年
select first 1 current,current - 2 units minute from systables --当前日期减2分钟
select first 1 current,date(current) - 2 units day,extend(date(current),year to minute) - 2 units minute from systables

总结
时间格式的字段可以直接和integer进行加减运算，需要注意的是加减运算的参与字段单位必须统一，例如 extend (year to minute)的最小单位是分钟，那么只能和分钟进行运算
例如
select first 1 current,date(current) - 2 units day,extend(date(current),year to minute) - 2 units minute from systables
而不能
select first 1 current,date(current) - 2 units day,extend(date(current),year to minute) - 2 units second from systables 会报错

today current类似于oracle的sysdate
informix的日期的format可以参考informix的global support GLS User's Guide
informix也有to_char() to_date()函数，当然跟oracle的用法完全不同[@more@]