有个SQL定时任务,每个月1号,查询上个月1号到上个月最后一天的数据,需要在SQL里面确定时间范围,hive数据表里面的时间格式是yyyyMMdd
这个问题,涉及几个时间函数的综合应用,关键点是:
时间范围要小于等于上个月最后一天,同时大于等于上个月第一天
上个月最后一天
当前时间是当月第一天:trunc(CURRENT_TIMESTAMP,'MM'), trunc(sysdate, 'mm'),返回当月第一天
当月第一天的前一天,也就是上个月的最后一天:date_sub(trunc(CURRENT_TIMESTAMP,'MM'),1),date_sub(string startdate,int days),返回开始日期startdate减少days天后的日期
现在的日期格式是yyyy-MM-dd,需要转成yyyyMMdd,先转换成时间戳: unix_timestamp(date_sub(trunc(CURRENT_TIMESTAMP,'MM'),1),'yyyy-MM-dd')
最后转换成yyyyMMdd:from_unixtime(unix_timestamp(date_sub(trunc(CURRENT_TIMESTAMP,'MM'),1),'yyyy-MM-dd'),'yyyyMMdd')
上个月第一天
首先让时间回到上个月,上个月的今天:add_months(CURRENT_TIMESTAMP,-1)
然后求得上个月的第一天:trunc(add_months(CURRENT_TIMESTAMP,-1),'MM')
最后从yyyy-MM-dd转换成yyyyMMdd:from_unixtime(unix_timestamp(trunc(add_months(CURRENT_TIMESTAMP,-1),'MM'),'yyyy-MM-dd'),'yyyyMMdd')
综上所述,最终的时间范围如下:
bussiness_date >= from_unixtime(unix_timestamp(trunc(add_months(CURRENT_TIMESTAMP,-1),'MM'),'yyyy-MM-dd'),'yyyyMMdd')
and bussiness_date<= from_unixtime(unix_timestamp(date_sub(trunc(CURRENT_TIMESTAMP,'MM'),1),'yyyy-MM-dd'),'yyyyMMdd')