hive—常用的日期函数

select now();
select current_timestamp();
--2024-12-11 17:02:02:616

--返回当前的时间
select from_UTC_timestamp(current_timestamp(),"GMT+8")
--2024-12-12 18:28:03.209

3、datediff(endDate, startDate) 计算日期相差天数

--其实就是用前一个日期减去后一个日期，想要得到正数的日期相差格式则需要把大的日期放在前边

select datediff('2022-02-22','2022-02-20');
--2

select datediff('2022-01-22','2022-02-20');
-- -29

4、months_between(endDate, startDate) 日期相差月数

select months_between('2022-06-16','2022-02-12');
--4.12903226

select floor(months_between('2022-06-16','2022-02-12'));
--4

5、date_add(startDate, numDays) 日期加N天

select date_add('2022-02-22',3)
--2022-02-25

select date_add('2022-02-22',-3)
--2022-02-19

6、date_sub(startDate, numDays) 日期减N天

select date_sub('2022-02-22',3)
--2022-02-19
 
select date_sub('2022-02-22',-3)
--2022-02-25

7、add_months(startDate, numMonths) 日期加N月

select add_months('2022-02-22',3);
--2022-05-22
 
select add_months('2022-02-22',-3);
--2021-11-22

但是在hivesql和spark sql 里 add_months(startDate, numMonths) 当startDate为月份最后一天的30号时，取不到相加或相减后月份的最后一天，例如

select add_months('2022-09-30',1);
--2022-10-30  --非往后一个月的最后一天

改成：加多一步判断是否是最后一天
select (case when month('${yyyy-MM-dd}') <> month(date_add('${yyyy-MM-dd}', 1)) then last_day(add_months('${yyyy-MM-dd}',-6)) else add_months('${yyyy-MM-dd}',-6) end) DD

8、last_day(date) 日期所在月份的最后一天

select last_day('2022-02-22');
--2022-02-28

9、 next_day(startDate, dayOfWeek) 指定日期后的第一个星期几（星期参数为英文缩写）

select next_day('2022-02-22','MON')
--2022-02-28

10、from_unixtime：转化unix时间戳到当前时区的时间格式


select from_unixtime(1641044052,'yyyyMMdd');
--输出：20220101

11、unix_timestamp：转换到UNIX时间戳

--当前时间
select unix_timestamp();
--输出：1665803372
 
--yyyy-MM-dd HH:mm:ss日期
select unix_timestamp('2022-01-01 13:34:12');
--输出：1641044052

12、to_date：返回时间中的年月日

select to_date('2022-01-01 13:34:12');
--输出：2022-01-01

13、year、month、day、hour、minute、second：返回时间的年、月、日、时、分、秒

--年
select year('2021-12-31 11:32:12');
select year('2021-12-31');
--输出：2021
 
--月
select month('2021-12-31 11:32:12');
select month('2021-12-31');
--输出：12
 
--日
select day('2021-12-31 11:32:12');
select day('2021-12-31');
--输出：31
 
--时
select hour('2021-12-31 11:32:12');
--输出：11
 
--分
select minute('2021-12-31 11:32:12');
--输出：32
 
--秒
select second('2021-12-31 11:32:12');
--输出：12

14、weekofyear：返回指定日期所在一年中的星期号，范围为0到53

即第几个星期

select weekofyear('2022-05-05');
--输出：18

15、trunc:为指定元素而截去的日期值

参数： YEAR、YYYY、YY、MON、MONTH、MM

--查询当月第一天MM/MONTH/MON
select trunc('2020-12-03','MM');
select trunc('2020-12-03','MONTH');
select trunc('2020-12-03','MON');
--输出：2020-12-01

--查询当年第一天YEAR/YY/YYYY
select trunc('2020-12-03','YYYY');
select trunc('2020-12-03','YEAR');
select trunc('2020-12-03','YY');
--输出：2020-12-01

16、date_trunc：

参数：YEAR、YYYY、YY、MON、MONTH、MM、DAY、DD、HOUR、MINUTE、SECOND、WEEK、QUARTER

select date_trunc("HOUR" ,"2022-12-12T09:32:05.359");
--输出：2022-12-12 09:00:00 

select date_trunc("YEAR" ,"2022-12-12T09:32:05.359");
--输出：2022-01-01 00:00:00 --本年第一天

select date_trunc("MON" ,"2022-12-12T09:32:05.359");
--输出：2022-12-01 00:00:00  --本月第一天

select date_trunc("WEEK" ,"2024-12-12T09:32:05.359");
--输出：2024-12-09 00:00:00  --本周第一天

select date_trunc("QUARTER" ,"2024-12-12T09:32:05.359");
--输出：2024-10-01 00:00:00  --本季度第一天

17、date_format：对时间日期进行格式化

select date_format('2022-04-08', 'y');
--输出：2022
 
select date_format('2022-04-08', 'yyyy');
--输出：2022
 
select date_format('2022-04-08', 'yyyy-MM');
--输出：2022-04
 
select date_format('2022-04-08 10:10:01', 'yyyy-MM');
--输出：2022-04
 
select date_format('2022-04-08', 'yyyy-MM-dd');
--输出：2022-04-08

18、dayofmonth：查询当月第几天、dayofweek：查询周几

--dayofmonth
select dayofmonth('2024-12-12')
--输出：12
 
--dayofweek(1 = Sunday, 2 = Monday, ..., 7 = Saturday)
select dayofweek('2024-12-12')
--输出：5  --5=Thursday

19、next_day：取当前天的下一个周一

--取当前天的下一个周一
select next_day('2024-12-12','MO');
--输出：2024-12-16
 
--取当前周的周一
select date_add(next_day('2024-12-12','MO'),-7);
--输出：2024-12-09
 
--取当前周的周日
select date_add(next_day('2024-12-12','MO'),-1) this_sun;
--输出：2024-12-15

20、pmod(int a, int b)：返回a除b的余数的绝对值

--计算2024-12-12是星期几
select pmod(datediff('2024-12-12','2024-01-01') + 1,7)
--4  --星期四

--计算今天是周几
select (case
when pmod(datediff(current_date(),date_trunc("YEAR",current_date())) + 1,7) = 1 then concat(current_date(),'(周一)')
when pmod(datediff(current_date(),date_trunc("YEAR",current_date())) + 1,7) = 2 then concat(current_date(),'(周二)')
when pmod(datediff(current_date(),date_trunc("YEAR",current_date())) + 1,7) = 3 then concat(current_date(),'(周三)')
when pmod(datediff(current_date(),date_trunc("YEAR",current_date())) + 1,7) = 4 then concat(current_date(),'(周四)')
when pmod(datediff(current_date(),date_trunc("YEAR",current_date())) + 1,7) = 5 then concat(current_date(),'(周五)')
when pmod(datediff(current_date(),date_trunc("YEAR",current_date())) + 1,7) = 6 then concat(current_date(),'(周六)')
else concat(current_date(),'(周日)') end) WEEK

--输出：2024-12-12(周四)

21、常用日期取数：

1、from_unixtime(unix_timestamp(,),)用法

--获取前一天的日期
select cast(from_unixtime(unix_timestamp('20221001','yyyyMMdd')-1,'yyyyMMdd') as bigint)
--输出：20220930
 
--转换日期格式
select from_unixtime(unix_timestamp('20211023','yyyyMMdd'),'yyyy-MM-dd')
--输出：2021-10-23

2、当前周属于哪个月

--这周内哪个月份所占天数多就算哪个月
SELECT month(date_sub(next_day('2024-12-12','Mon'),4))
--输出：12

3、当前周属于哪一周

SELECT 
case when day(date_add(next_day('2024-12-12','Mon'),-4)) <=7 then '第一周'
when day(date_add(next_day('2024-12-12','Mon'),-4)) >7 and day(date_add(next_day('2024-12-12','Mon'),-4))<=14 then '第二周'
when day(date_add(next_day('2024-12-12','Mon'),-4)) >14 and day(date_add(next_day('2024-12-12','Mon'),-4))<=21 then '第三周'
when day(date_add(next_day('2024-12-12','Mon'),-4)) >21 and day(date_add(next_day('2024-12-12','Mon'),-4))<=28 then '第四周'
when day(date_add(next_day('2024-12-12','Mon'),-4)) >28 then '第五周'
end as week_key   --周
--输出：第二周

4、生成连续的天表、小时表、分钟表

1）生成连续的天

---生成连续的天
select
date_add(start_date,pos) as dt
from
(
   select 
  '2022-12-01' as start_date
 ,'2022-12-04' as end_date
)t
lateral view posexplode(split(repeat(',',datediff(end_date,start_date)),',')) tab as pos,val;

输出结果：

2）生成连续小时

---生成连续的小时
select
from_unixtime(t1.start_time+tab.pos*3600) as continue_time
from
(
    select
    unix_timestamp('2022-12-01 00:00:00') as start_time   ---生成连续小时的开始时间
		,unix_timestamp('2022-12-01 03:00:00') as end_time   ---生成连续小时的结束时间
)t1
lateral view posexplode(split(repeat(',',cast((end_time-start_time)/3600 as int)),',')) tab as pos,val;

输出结果：

3）生成连续分钟

---生成连续的分钟
select
from_unixtime(t1.start_time+tab.pos*60) as continue_time
from
(
    select
    unix_timestamp('2021-12-01 00:00:00') as start_time   ---生成连续分钟的开始时间
		,unix_timestamp('2021-12-01 00:03:00') as end_time   ---生成连续分钟的结束时间
)t1
lateral view posexplode(split(repeat(',',cast((end_time-start_time)/60 as int)),',')) tab as pos,val;

输出结果:

4）生成连续秒

---生成连续的秒
select
from_unixtime(t1.start_time+tab.pos) as continue_time
from
(
    select
    unix_timestamp('2021-12-01 00:00:00') as start_time   ---生成连续秒的开始时间
		,unix_timestamp('2021-12-01 00:00:03') as end_time   ---生成连续秒的结束时间
)t1
lateral view posexplode(split(repeat(',',cast(end_time-start_time as int)),',')) tab as pos,val;

输出结果：

5）生成连续月

---生成连续的月
SELECT
	SUBSTR( add_months ( start_date, pos ), 1, 10 ) AS month_key 
FROM
	(SELECT '2023-03-01' AS start_date ) tmp 
	lateral VIEW posexplode ( split ( space( 3 ), '' ) ) t AS pos, val;

输出结果：

6）生产今年内的连续月

---生产今年内的连续月
SELECT
	SUBSTR( add_months ( 
	FROM_UNIXTIME( unix_timestamp( SUBSTR( start_date, 1, 4 ), 'yyyy' ))
	, pos ), 1, 10 ) AS month_key 
FROM
	(SELECT concat(substr(add_months('2023-03-01',1),0,4),'-01-01') AS start_date ) tmp 
	lateral VIEW posexplode ( split ( space( 11 ), '' ) ) t AS pos, val ;

输出结果：

5、时间和秒数互相转换

1）当前时间转换成秒数

SELECT
  hour('2023-06-06 12:00:00') * 3600 + minute('2023-06-06 12:00:00') * 60 + second('2023-06-06 12:00:00')
--输出：43200

2）秒数转换为时间（时分秒）

select
  concat(
    case
      when length(cast(floor(43200 / 3600) as string)) = 1 then concat('0', floor(43200 / 3600), ':')
      when length(cast(floor(43200 / 3600) as string)) = 2 then concat(floor(43200 / 3600), ':')
      else '00:'
    end,
    case
      when length(cast(floor(43200 % 3600 / 60) as string)) = 1 then concat('0', floor(43200 % 3600 / 60), ':')
      when length(cast(floor(43200 % 3600 / 60) as string)) = 2 then concat(floor(43200 % 3600 / 60), ':')
      else '00:'
    end,
    case
      when length(cast(floor(43200 % 3600 % 60) as string)) = 1 then concat('0', floor(43200 % 3600 % 60))
      when length(cast(floor(43200 % 3600 % 60) as string)) = 2 then floor(43200 % 3600 % 60)
      else '00'
    end
  )
--输出：12:00:00

22、工作特殊日期取数(有一些为工作环境内置函数)

1）当月最后一天更新完整月份时间范围的数据

例如，2024-09-30更新近半年2024-04-01至2024-09-30的数据

where month('${yyyy-MM-dd}') != month(date_add('${yyyy-MM-dd}', 1)) -- 月末最后一天
and busi_date <= default.pretradedate(add_months(default.datekey2date(concat('${YYYYMM}', '01')), 1), 1)
and busi_date >= add_months(default.datekey2date(concat('${YYYYMM}', '01')), -5)

2024-10-28 例子：
month('${yyyy-MM-dd}')：10
date_add('${yyyy-MM-dd}', 1) ：2024-10-29
month(date_add('${yyyy-MM-dd}', 1))：10
concat('${YYYYMM}', '01')：20241001
default.datekey2date(concat('${YYYYMM}', '01')：2024-10-01
add_months(default.datekey2date(concat('${YYYYMM}', '01')), 1)：2024-11-01
default.pretradedate(add_months(default.datekey2date(concat('${YYYYMM}', '01')), 1), 1)：2024-10-31
add_months(default.datekey2date(concat('${YYYYMM}', '01')), -5)：2024-05-01

2）上月最后一个交易日

select default.pretradedate(default.datekey2date(concat('${YYYYMM}', '01')), 1) DD

3）本月第一个交易日

select default.pretradedate(default.pretradedate(default.datekey2date(concat('${YYYYMM}', '01')),1),-1)

4）如果今天不是本月自然日最后一天,则取上月的最后一个交易日,否则取当月最后一个交易日

select (CASE WHEN MONTH('${yyyy-MM-dd}') != MONTH(date_add('${yyyy-MM-dd}',1)) 
THEN default.pretradedate(add_months(default.datekey2date(concat('${YYYYMM}', '01')),1), 1)
else default.pretradedate(default.datekey2date(concat('${YYYYMM}', '01')),1) end) DD

5）如果今天不是本月自然日最后一天，则取6个月前的第一天，否则取5个月前的第一天

--如果今天不是本月自然日最后一天，则取6个月前的第一天，否则取5个月前的第一天
select (CASE WHEN MONTH('${yyyy-MM-dd}') != MONTH(date_add('${yyyy-MM-dd}',1)) 
then add_months(default.datekey2date(concat('${YYYYMM}', '01')), -5)
else add_months(default.datekey2date(concat('${YYYYMM}', '01')), -6) end) DD

--参数：2024-12-12 202412
--输出：2024-06-01

--参数：2024-12-31 202412
--输出：2024-07-01

6）任务交易日执行，取次月第一个交易日数据

例如event_date ='2024-10-31'的数据会在busi_date = '2024-11-01'的数据才出现，而busi_date = '2024-10-31'的数据是没有的，需要统计为10月份的数据，因此需要判断

where '${yyyy-MM-dd}'= default.pretradedate(default.pretradedate(default.datekey2date(concat('${YYYYMM}', '01')),1),-1) -- 本月第一个交易日
--任务交易日执行，取次月第一个交易日数据
and busi_date = default.pretradedate(default.pretradedate(default.datekey2date(concat('${YYYYMM}', '01')),1),-1)
and event_date <= default.pretradedate(default.datekey2date(concat('${YYYYMM}', '01')), 1)
and event_date >= add_months(default.datekey2date(concat('${YYYYMM}', '01')), -6)

7）如果当日是交易日则取当日的数据，如果当日非交易日则取前一个交易日的数据

select default.pretradedate(date_add('${yyyy-MM-dd}',1),1)

8）每日更新近半年截至当日的数据，如果最后一天是30号，则取6个月前的最后一天

where A.busi_date >(case when month('${yyyy-MM-dd}') <> month(date_add('${yyyy-MM-dd}', 1)) then last_day(add_months('${yyyy-MM-dd}',-6))
else add_months('${yyyy-MM-dd}',-6) end)
and A.busi_date <= '${yyyy-MM-dd}'

9）取客户开户以来第3至第9个交易日的日期

select pty_id,dt
from
(select t.pty_id,
date_add(start_date,pos) as dt
from 
(select A.pty_id,
default.pretradedate(oact_date,-3) start_date,
default.pretradedate(oact_date,-9) end_date
from tmp_BROK_INDV_CUST_BASE_INFO A) t
lateral view posexplode(split(repeat(',',datediff(end_date,start_date)),',')) tab as pos,val
) S 
where default.IsTradeDay(dt) = 1  --判定是否是交易日

10）取本季度第一天

select (CASE 
        WHEN month('${yyyy-MM-dd}') BETWEEN 1 AND 3 THEN concat(year('${yyyy-MM-dd}'), '-01-01')
        WHEN month('${yyyy-MM-dd}') BETWEEN 4 AND 6 THEN concat(year('${yyyy-MM-dd}'), '-04-01')
        WHEN month('${yyyy-MM-dd}') BETWEEN 7 AND 9 THEN concat(year('${yyyy-MM-dd}'), '-07-01')
        ELSE concat(year('${yyyy-MM-dd}'), '-10-01')
    END) DD