Informix日期获取上周上月昨天去年SQL

遇到同步数据类工作通常需要对日期进行转换,记录一下Informix SQL遇到的写法。
标准sql语法和Informix SQL 函数可百度了解.

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;
testIdinsert_time
12018-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;
testIdcur_time
12018-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;
testIdcur_time
12018-11-28 16:31:18


3、获取上个小时,昨天和上个月

理解了相关函数,实现方式非常多,接下来会举一些例子。

  • 昨天
select testid, today - 1 as yeaterday
,sysdate - 1 units day as yeaterday_t
from test_tab;
testIdyeaterdayyeaterday_t
12018-11-272018-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;
testIdlasthourlasthour1lasthour2
12018-11-28 16:29:032018-11-28 16:29:032018-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;
testIdcur_dateweekstrwdaylastsundaylastmonday
12018-11-28Wednesday32018-11-252018-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;
testIdcur_datelastmon_strddaylastmon_endlastmon_beginlastmon_begin1
12018-11-28201810282018-10-312018-10-01 00:00:002018-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
testIdcur_datethismonthlastmon_beginlastmon_end
12018-11-282018-11-012018-10-012018-10-31 00:00:00

informix里字符串拼接使用 "||"
mdy(m,d,y)

转载于:https://www.cnblogs.com/missfox18/p/10033740.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值