当前位置： 首页 > news >正文

hive—常用的日期函数

news 2025/7/18 18:22:43

目录

1、current_date 当前日期

2、now() 或 current_timestamp() 当前时间

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

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

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

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

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

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

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

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

11、unix_timestamp：转换到UNIX时间戳

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

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

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

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

16、date_trunc：

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

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

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

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

21、常用日期取数：

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

2、当前周属于哪个月

3、当前周属于哪一周

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

1）生成连续的天

2）生成连续小时

3）生成连续分钟

​编辑4）生成连续秒

5）生成连续月

6）生产今年内的连续月

5、时间和秒数互相转换

1）当前时间转换成秒数

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

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

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

2）上月最后一个交易日

3）本月第一个交易日

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

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

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

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

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

1、current_date 当前日期

select current_date();
--2024-12-11

2、now() 或 current_timestamp() 当前时间

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');
--2select datediff('2022-01-22','2022-02-20');
-- -29

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

select months_between('2022-06-16','2022-02-12');
--4.12903226select 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-25select date_add('2022-02-22',-3)
--2022-02-19

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

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

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

select add_months('2022-02-22',3);
--2022-05-22select 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');
--输出：2022select date_format('2022-04-08', 'yyyy');
--输出：2022select date_format('2022-04-08', 'yyyy-MM');
--输出：2022-04select date_format('2022-04-08 10:10:01', 'yyyy-MM');
--输出：2022-04select 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
(selectunix_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
(selectunix_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
(selectunix_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）生成连续月

---生成连续的月
SELECTSUBSTR( 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）生产今年内的连续月

---生产今年内的连续月
SELECTSUBSTR( 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）当前时间转换成秒数

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

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

selectconcat(casewhen 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,casewhen 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,casewhen 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}'

http://www.lryc.cn/news/504626.html

相关文章：

  • HTML零基础入门教学
  • vue3 父组件调用子组件 el-drawer 抽屉
  • Java中常用算法之选择排序算法
  • UNIX简史
  • React和Vue中暴露子组件的属性和方法给父组件用，并且控制子组件暴露的颗粒度的做法
  • uniapp 常用的指令语句
  • python学opencv|读取图像（十四）BGR图像和HSV图像通道拆分
  • C# 结构体和类
  • D9741是一块脉宽调制方三用于也收路像机和笔记本电的等设备上的直流转换器。在便携式的仪器设备上。
  • 题目 1738: 排序
  • 爬虫逆向学习(十四)：分享一下某数通用破解服务开发经验
  • 《Vue进阶教程》第十一课：响应式系统介绍
  • rpc设计的再次思考20251215(以xdb为核心构建游戏框架)
  • pydub AudioSegment增加音频文件音量并保存- python 实现
  • IT 新突破！远程控制电脑技术造就工作与学习新方向！
  • LabVIEW起落架震台检测
  • Day24 C++ 接口（抽象类）
  • UE5 关于画质、机能与开发成本的思考
  • IOS学习路线图
  • HICE-day6
  • 第100+33步 ChatGPT学习：时间序列EMD-ARIMA-LSTM模型
  • （C语言）双向链表
  • 青少年编程与数学 02-004 Go语言Web编程 04课题、接收和处理请求
  • Unity全局光照详解
  • 计算机网络知识点全梳理（三.TCP知识点总结）
  • ELK Stack 安装、配置以及集成到 Java 微服务中的使用
  • list_
  • 电机驱动，为什么不需要变速器？
  • how to write 述职pptx as a tech manager
  • 关于QMessageBox的一些使用总结和避坑指南