HQL时间函数应用

一、Hive时间函数的应用

第一节、时间函数（初级）

1、获取当前系统时间

1.1获取当前时间戳 current_timestamp 返回值：timestamp

select current_timestamp（）;
返回结果：2023-08-14 19:39:54.041 

1.2 获取当前时间 current_date 返回值：date

select current_date（）;
返回结果：2023-08-14  

1.3 仅Spark sql支持：now()获取当前时间

select now();
返回结果：2023-09-20 14:27:30.955

2、当前UNIX时间戳: unix_timestamp() 返回值：bigint

2.1 获取当前UNIX时间戳函数

函数	格式	返回值	说明
unix_timestamp	unix_timestamp()	bigint	获得当前时区的UNIX时间戳

select unix_timestamp();
1472105939

2.2 日期转UNIX时间戳函数--转换格式为"yyyy-MM-dd HH:mm:ss"

函数	格式	返回值	说明
unix_timestamp	unix_timestamp(string date)	bigint	转换格式为"yyyy-MM-dd HH:mm:ss"的日期到UNIX时间戳。 转化失败，则返回0

select unix_timestamp('2016-08-25 13:02:03');
1472101323

2.3 指定格式日期转UNIX时间戳函数unix_timestamp--转换格式为"yyyyMMdd HH:mm:ss"

函数	格式	返回值	说明
unix_timestamp	unix_timestamp(string date, string pattern)	bigint	转换格式为"yyyyMMdd HH:mm:ss"的日期到UNIX时间戳。转化失败，则返回0

select unix_timestamp('20160825 13:02:03','yyyyMMdd HH:mm:ss');
1472101323

总结：

unix_timestamp : 转换成时间戳，他是可以将符合时间格式转换成按秒计数的函数。例如：'YYYY-MM-DD','YYYYMMDD'，'YYYY/MM/DD',以及后面的时分秒。

3、日期函数UNIX时间戳转日期函数：from_unixtime() 返回值：string

函数	格式	返回值	说明
from_unixtime	from_unixtime(bigint unixtime[, string format])	string	转化UNIX时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）到当前时区的时间格式

hive (temp)> select from_unixtime(1323308943,'yyyyMMdd') ;
20111208
hive (temp)> select from_unixtime(1323308943,'yyyy-MM-dd');
2011-12-08

  

eg1:获取当前时间：年月日格式

select from_unixtime(unix_timestamp(),'yyyy-MM-dd');
2023-08-14

eg2:获取当前时间 时分秒格式：yyyy-MM-dd HH:mm:ss

select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss');
2023-08-14 17:39:21 

4，date_format日期格式提取转换

date_format(date/timestamp/string ts, string fmt) 返回值：string

将date/timesatmp/str转化为日期格式fmt指定格式的字符串值

注意：日期字符串必须满足yyyy-MM-dd格式  

Hive应用：提取年份

select date_format('2021-10-20','yyyy');
2021

Spark应用：小时取整

取小时（可加：00）
select date_format(now(),'HH:00');
15:00


取秒
select date_format(now(),'ss');
50

5、日期时间转日期函数: to_date():日期时间字段中的日期(年月日)部分 返回值：string

函数	格式	返回值	说明
to_date	to_date(string timestamp)	string	返回日期时间字段中的日期部分

select to_date('2023-12-08 10:03:01') ;
2023-12-08

6、年月日秒周提取

日期提取年函数: year()

函数	格式	返回值	说明
year	year(string date)	int	返回日期中的年

hive (temp)> select year('2016-12-08 10:03:01');
2016


hive (temp)> select year('2016-12-08');
2016

日期提取月函数: month()

函数	格式	返回值	说明
month	month(string date)	int	返回日期中的月份

hive (temp)> select month('2016-12-08 10:03:01') ;
12


hive (temp)> select month('2016-11-08') ;
11

日期提取天函数: day()

函数	格式	返回值	说明
day	day(string date)	int	返回日期中的天

hive (temp)> select day('2016-12-08 10:03:01') from dual;
8


hive (temp)> select day('2016-11-18') from dual;
18

日期提取小时函数: hour()

函数	格式	返回值	说明
hour	hour(string date)	int	返回日期中的小时

hive (temp)> select hour('2016-12-08 10:03:01') ;
10

日期提取分钟函数: minute()

函数	格式	返回值	说明
minute	minute(string date)	int	返回日期中的分钟

hive (temp)> select minute('2016-12-08 10:03:01') from dual;
3

日期提取秒函数: second()

函数	格式	返回值	说明
second	second(string date)	int	返回日期中的秒

hive (temp)> select second('2016-12-08 10:03:01') from dual;
1

日期提取周函数: weekofyear()当前日期是该年的第几周

函数	格式	返回值	说明
weekofyear	weekofyear(string date)	int	返回日期在当前的周数

select weekofyear('2016-12-08 10:03:01');
49

日期提取天函数: dayofyear()当前日期是一年的第几天

select dayofyear('2016-12-08 10:03:01');

7、日期比较函数: datediff(string enddate, string startdate)

函数	格式	返回值	说明
datediff	datediff(string enddate, string startdate)	int	返回结束日期减去开始日期 的天数

eg1 ：计算给定的2个时间节点之间的天数差

---计算给定的2个时间节点之间的天数差
select datediff('2023-12-08','2023-08-02') ;    ---128天

eg2：计算当前时间 到 2023-11-18 的天数差

----计算当前时间 到 2023-11-18 的天数差
select datediff('2023-11-18',from_unixtime(unix_timestamp(),'yyyy-MM-dd'));    
96天

8、日期增加函数: date_add(string startdate, int days)

函数	格式	返回值	说明
date_add	date_add(string startdate, int days)	string	返回开始日期startdate增加days天后的日期

hive (temp)> select date_add('2016-12-08',10);
2016-12-18

eg:当前日期基础上加7天

#当前日期基础上加7天
select date_add(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),7);

9、日期减少函数:date_sub (string startdate, int days)

函数	格式	返回值	说明
date_sub	date_sub(string startdate, int days)	string	返回开始日期startdate减少days天后的日期

hive (temp)> select date_sub('2016-12-08',10);
2016-11-28

eg:当前日期基础上减7天

select date_sub(from_unixtime(unix_timestamp(),'yyyy-MM-dd'),7) ;
2016-08-18

10、 获取某月最后一天last_day(expr)

expr：一个 DATE 表达式

SELECT last_day('2023-02-12');
2023-02-28

11、next_day(expr, dayOfWeek) 获取当前日期的下一周里“星期几”的日期

next_day(STRING start_date, STRING day_of_week)返回 当前日期下个 周几的 日期；

例1：求当前日期的下周一，day_of_week 参数输入的是MO，则返回的是下周周一的日期；

SELECT next_day(current_date(), 'MO')
2023-09-25

第二个参数：周几缩写（可以小写）或全称：

下个周一（‘MO’）、下个周二（‘TU’）、下个周三（‘WE’）、下个周四（‘TH’）、下个周五（‘FR’）、下个周六（‘SA’）、下个周日（‘SU’）；

例2：返回指定日期的‘2023-08-15’下个周一的日期

SELECT next_day('2023-08-15', 'TU');
2023-08-22

-- 除了周一，其他不适用，需要更新
-- 本周一
SELECT date_add(next_day(current_date(), 'MO'), -7)
-- 上周一
SELECT date_add(next_day(current_date(), 'MO'), -14)
-- 下周一
SELECT next_day(current_date(), 'MO')
-- 下下周一
SELECT date_add(next_day(current_date(), 'MO'), 7)

12、求“时间”为当年的第几季度（仅spark3支持）

select quarter('2023-09-20')
返回结果：3

第二节、时间函数（中级--年月加减）

1、add_months月份加减

add_months函数，负数代表往历史方向减月份， 正数代表往未来方向加月份

举例：当前时间的上个月这个时间

select add_months(CURRENT_DATE,-1) ;
返回结果：2023-08-19 

2、取当前格式化时间的前30天

select from_unixtime(unix_timestamp('2023-09-19','yyyy-MM-dd')-30*86400,'yyyy-MM-dd');
返回结果：2023-08-20 

3、trunc()：获取本月第一天

select trunc('2023-09-19','MM');
返回结果： 2023-09-01 

3.1 trunc()：获取上个月第一天

select add_months(trunc('2023-09-19','MM'),-1);
返回结果：2023-08-01 

3.2 trunc()：获取下个月第一天

select add_months(trunc('2023-09-19','MM'),1);
返回结果：2023-10-01

3.3 trunc()：获取本月最后一天

select date_sub(trunc(add_months('2023-09-19',1),'MM'),1);
返回结果：2023-09-30 

3.4 trunc()：获取上个月最后一天下个月最后一天

select date_sub(trunc('2023-09-20','MM'),1);
返回结果：2023-08-31   2023-10-31

3.5 trunc()：获取下个月最后一天

select date_sub(trunc(add_months('2023-09-20',2),'MM'),1);
返回结果：2023-10-31

3.6 trunc()：获取当年第一天

select trunc('2023-09-19','YY');
返回结果： 2023-01-01 

3.7 trunc()：获取当年最后一天

4、last_day(STRING date) :返回月末日期

返回月末日期，返回的是 "yyyy-MM-dd"格式的日期。

5、获取本周任意一天

6、每天的上周日–本周六：

应用场景：统计每周的访问量、点击次数等等

每天的上周日：

select date_sub(next_day('2023-09-20','MO'),8);
返回结果：2023-09-17

每天的本周六：

select date_sub(next_day('2023-09-20','MO'),2);
返回结果：2023-09-23

7、months_between 两个日期之间的月数(仅spark3支持)

select months_between ('2023-09-20'，'1992-11-18')；
370.06451613

第三节、时间函数应用案例（同比or环比）

1、同比和环比概念

SQL同比环比_sql 环比-CSDN博客^v94^insert_down1&utm_term=%E5%90%8C%E6%AF%94%E7%8E%AF%E6%AF%94sql&spm=1018.2226.3001.4187

数据分析SQL环比增长率、同比增长率_大数据sql 年环比-CSDN博客^v94^insert_down1&spm=1018.2226.3001.4187

二、时间函数应用的异常案例

1，json中提取时间戳

示例：

info对应的字段值为如：48330a9a94d73db188c78ae6bdc58cbe|0|5|0|11|cxvideo_relate|1605655303|1|516||0|OvPMS|0|92201|1605655303076e5630d435593||a|2|0|comos%3Aiznctke14900

需求：

要拿到时间戳并转换为小时从经过group by操作的表中抽取出来

分析：

先split分割值，拿到时间戳1605655303 ，然后转换为bigint后再转换为hour函数需要的时间格式的形式，再利用Hour函数读取其中的小时出来。具体代码形如：

max(hour(from_unixtime(cast(split(info,'\\\|')[6] as BIGINT),'yyyy-MM-dd HH:mm:ss'))) as hour

2，数据类型不一致报错

报错：

Caused by: java.lang.ClassCastException: java.sql.Timestamp cannot be cast to java.lang.String

at java.lang.String.compareTo(String.java:111)

at org.apache.hadoop.hive.ql.io.orc.RecordReaderImpl.compareToRange(RecordReaderImpl.java:2295)

atorg.apache.hadoop.hive.ql.io.orc.RecordReaderImpl.evaluatePredicateRange(RecordReaderImpl.java:2444)at org.apache.hadoop.hive.ql.io.orc.RecordReaderImpl.evaluatePredicate(RecordReaderImpl.java:2383)

at org.apache.hadoop.hive.ql.io.orc.RecordReaderImpl.pickRowGroups(RecordReaderImpl.java:2595)

at org.apache.hadoop.hive.ql.io.orc.RecordReaderImpl.readStripe(RecordReaderImpl.java:2658)

原代码：
SELECT 
    date_format(a.start_time, 'yyyy-MM-dd') AS `date`
FROM 
    a
WHERE 
AND a.start_time < date_sub(CURRENT_DATE(),-8)

根本原因：

等式两边数据类型不一致。timestamp函数date类型与string类型比较大小时，需要先将timestamp类型转成date类型再去比较。(方法不唯一,应该是只要能进行自动类型转换就可以比较)

解决办法：

把a.start_time的int/string数值类型转换成date类型即可与右边比较

from_unixtime(unix_timestamp( a.start_time, 'yyyyMMdd') , 'yyyy-MM

-dd') < date_sub(CURRENT_DATE(),-8)

3，date_format 报错NULL值

案例1：提取2015/04/08中的年

select date_format('2015/04/08', 'y');

--null 识别不了/ 这种分隔符

解决办法：regexp_replace替换

select date_format(regexp_replace('2015/04/08', '/', '-'), 'y');
--2015

----------------------------------------------------------分割线君---------------------------------------------------------

案例2：提取2015/04/08中的月份or年月

select month('2015/04/08');

--null 无法识别

解决办法：

1，提取年月

select date_format(regexp_replace('2015/04/08', '/', '-'), 'y-MM');
--2015-04    

2，提取月：先将2015/04/08替换成 2015-04-08

select month(regexp_replace('2015/04/08', '/', '-'));
-- 4 

----------------------------------------------------------分割线君-----------------------------------------------------------

案例3：比较字段dayno时间大小取值范围

select date_format ('20230809','yyyy-MM-dd');

---NULL 报错空值

原因：

因为这里的dayno字段是20230809 int或string,并非date/timestamp/string ts类型，识别不到所以null异常

解决办法：

第一步：unix_timestamp先将20230809转换为时间戳

select unix_timestamp('20230809', 'yyyyMMdd')
1691510400 

第二步：from_unixtime再将上一步时间戳转换为年月日格式

select from_unixtime(unix_timestamp('20230809', 'yyyyMMdd') , 'yyyy-MM
-dd')；
2023-08-09 

总 结

date_format 的语法：

date_format(date/timestamp/string ts, string fmt)

• date_format函数如果第一个参数是字符串,连接符只能是-,别的都识别不了

4，yyyy-MM-dd大小写导致报错

问题背景：

任务需求查询获取dayno往前一天的数据，但这里的dayno每到月底的那一天就会直接读到该月的第一天，例如20230731这天需求是20230801这天的数据，但异常表现是读到20230701

select model, from_unixtime(unix_timestamp(dayno, 'yyyymmdd')+86400, 'yyyy-mm-dd') as dayno from wireless.wireless_daily_network_kpi_aggr  where dayno = 20230731 limit 100

原因：date数据类型大小写导致

因为这里的'yyyymmdd'中的mm是分钟，不是MM月

'yyyymmdd'中的mm是分钟，不是MM月

解决办法：改写mm大小写

select model, from_unixtime(unix_timestamp(dayno, 'yyyyMMdd')+86400, 'yyyy-MM-dd') as dayno from wireless.wireless_daily_network_kpi_aggr  where dayno = 20230731 limit 100

5，spark 任务报错

Spark任务报错异常：

java.lang.AssertionError: assertion failed

at scala.Predef$.assert(Predef.scala:208)

at org.apache.spark.sql.catalyst.util.DateTimeUtils$.extractSpecialValue

原因：date函数转变字段数值类型异常

三，其他

1，Hive中时区计算

Hive SQL 支持处理时间和日期数据类型，但其内置函数对于直接处理时区计算相对有限。Hive 通常默认使用服务器所在的时区，如果需要处理不同时区的数据，你通常需要自己实现逻辑或使用UDF（用户定义函数）。

时区相关计算，比如手动偏移时间：

SELECT from_utc_timestamp('2023-01-01 00:00:00', '+08:00') as beijing_time;
这将返回 UTC 时间加上 8 小时的时间，大致模拟北京时间（UTC+8）。

更复杂的时区转换需要以下方法：

计算时区差异：

如果是两个已知时区的时间计算，你可以自行计算时间差并进行加减。例如，从 UTC 转换到 UTC+8：

SELECT date_add(your_timestamp_column, INTERVAL 8 HOURS) FROM your_table;

使用 UDF：

如果 Hive 的内置函数不满足需求，你可以使用 Java 或其他支持的语言编写自定义的 UDF 来进行时区的转换操作。

外部工具处理：

在某些情况下，你可能需要在将数据加载到 Hive 之前或在从 Hive 中提取数据后，在外部使用程序或脚本进行时区转换。