HIVE日期函数计算

最新推荐文章于 2024-07-08 02:33:19 发布

潜移默化139

最新推荐文章于 2024-07-08 02:33:19 发布

阅读量1.2k

点赞数

文章标签： hive

本文链接：https://blog.csdn.net/qq_61938982/article/details/126927654

版权

SELECT replace(date_sub(to_date(from_unixtime(unix_timestamp('${runDt}','yyyyMMdd'))),1),'-',''); -- 昨天

SELECT replace(DATE_sub(NEXT_day(to_date(from_unixtime(unix_timestamp('${runDt}','yyyyMMdd'))),'MO'),7),'-',''); -- 本周周一

SELECT replace(date_sub(to_date(from_unixtime(unix_timestamp('${runDt}','yyyyMMdd'))),7),'-',''); -- 上周同期

SELECT replace(trunc(to_date(from_unixtime(unix_timestamp('${runDt}','yyyyMMdd'))),'MM'),'-',''); -- 当月月初

BETWEEN replace(TRUNC((to_date(from_unixtime(unix_timestamp('${runDt}','yyyyMMdd')))),'MM'),'-','') and '${runDt}'; -- 当月统计

SELECT replace(TRUNC((add_months(to_date(from_unixtime(unix_timestamp('${runDt}','yyyyMMdd'))),-1)),'MM'),'-',''); -- 上月月初

SELECT replace(add_months(to_date(from_unixtime(unix_timestamp('${runDt}','yyyyMMdd'))),-1),'-',''); -- 上月同期

SELECT replace(last_day(add_months(to_date(from_unixtime(unix_timestamp('${runDt}','yyyyMMdd'))),-1)),'-',''); -- 上月月末

select replace(add_months(CONCAT(year(to_date(from_unixtime(unix_timestamp('${runDt}','yyyyMMdd')))),'-',substr(concat('0',floor((month(to_date(from_unixtime(unix_timestamp('${runDt}','yyyyMMdd'))))+2)/3)*3+1),-2),'-01'),-3),'-',''); -- 季初

BETWEEN replace(add_months(CONCAT(year(to_date(from_unixtime(unix_timestamp('${runDt}','yyyyMMdd')))),'-',substr(concat('0',floor((month(to_date(from_unixtime(unix_timestamp('${runDt}','yyyyMMdd'))))+2)/3)*3+1),-2),'-01'),-3),'-','') and '${runDt}'; -- 当季统计