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@]
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@]
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/70612/viewspace-1027846/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/70612/viewspace-1027846/