informix常用時間運算和操作方式

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@]