1、准备测试表
由于Informix没有dual表，我们新建test_tab，并插入一条数据作为测试表。
建表
--建表
create table test_tab(
testId int,
insert_time datetime year to second default current year to second --插入时间 默认当前时间
);
--插入一条数据
insert into test_tab(testId) values(1);
查看测试表
--查询测试表
select * from test_tab;
testId insert_time
1 2018-11-28 16:31:18
2、日期转换
转换日期为指定字符串格式，如yyyy-MM-dd HH:mm:ss格式：
select testid, to_char(today, ‘%Y-%m-%d %H:%M:%S‘) as cur_time
from test_tab;
testId cur_time
1 2018-11-28 00:00:00
today 指今天 yyyy-MM-dd
转换字符串为日期格式，如字符串 ‘2018/11/28 16:31:18‘
select testid, to_date(‘2018/11/28 16:31:18‘, ‘%Y/%m/%d %H:%M:%S‘) as cur_time
from test_tab;
testId cur_time
1 2018-11-28 16:31:18
3、获取上个小时，昨天和上个月
理解了相关函数，实现方式非常多，接下来会举一些例子。
昨天
select testid, today - 1 as yeaterday
,sysdate - 1 units day as yeaterday_t
from test_tab;
testId yeaterday yeaterday_t
1 2018-11-27 2018-11-27 16:31:18
sysdate 指当前时间 yyyy-MM-dd HH:mm:ss
当前时间减去一天即为昨天此时
上个小时
select testid
,sysdate-1 units hour lasthour
,sysdate - interval(1) hour to hour lasthour1
,extend(sysdate-1 units hour,year to hour) lasthour2
from test_tab;
testId lasthour lasthour1 lasthour2
1 2018-11-28 16:29:03 2018-11-28 16:29:03 2018-11-28 16:00:00
extend 可以把日期处理成想要的精度
上周日期
select testid,today as cur_date
,to_char(today,‘%A‘) as weekstr
,weekday(today) as wday
,today -weekday(today) as lastsunday
,today -weekday(today)-6 as lastmonday
from test_tab;
testId cur_date weekstr wday lastsunday lastmonday
1 2018-11-28 Wednesday 3 2018-11-25 2018-11-19
weekday(today) 显示当前是周的第几天，可以理解为 dayOfWeek
day(today)则可表示dayOfMonth
上月日期
select testid,today as cur_date
,to_char(sysdate-1 units month,‘%Y%m‘) as lastmon_str
,day(today) as dday
,today -day(today) as lastmon_end
,to_date(to_char(sysdate-1 units month,‘%Y%m‘)|| ‘01‘, ‘%Y%m%d‘) as lastmon_begin
,extend(sysdate - 1 units month,year to month) as lastmon_begin1
from test_tab;
testId cur_date lastmon_str dday lastmon_end lastmon_begin lastmon_begin1
1 2018-11-28 201810 28 2018-10-31 2018-10-01 00:00:00 2018-10-01 00:00:00
select testid,today as cur_date
,mdy(month(today),1,year(today)) thismonth
,mdy(month(today)-1,1,year(today)) lastmon_begin
,mdy(month(today),1,year(today))-1 units day lastmon_end
from test_tab
testId cur_date thismonth lastmon_begin lastmon_end
1 2018-11-28 2018-11-01 2018-10-01 2018-10-31 00:00:00
informix里字符串拼接使用 "||"
mdy(m,d,y)