SQL使用技巧(4.1)Hive日期时间函数

专题：SQL使用技巧——实践是检验SQL函数的唯一标准

常用的格式化(format)标识符：

yyyy：四位年份
MM：月份，不足两位补0
dd：日期，不足两位补0
HH：小时，24小时制，不足两位补0
mm：分钟，不足两位补0
ss：秒，不足两位补0

一.常规计算函数说明

本章节每一行代码后都有运算说明和执行结果样例，例如 - - 返回当前系统日期 yyyy-MM-dd， 【2023-04-01】

1.1系统当前日期时间

selectcurrent_date()-- 返回当前系统日期  yyyy-MM-dd， 【2023-04-01】,current_date-- 返回当前系统日期  yyyy-MM-dd，带不带括号是一样的  【2023-04-01】,current_timestamp()-- 返回当前系统时间，格式为yyyy-MM-dd HH:mm:ss 【2023-04-01 16:13:39】,current_timestamp-- 返回当前系统时间，格式为yyyy-MM-dd HH:mm:ss，带不带括号是一样的 【2023-04-01 16:13:39】,unix_timestamp(current_timestamp)-- 返回当前系统时间的时间戳 【1680365619】;

原生hive中不支持

now()

的写法，经过加工的工具（TDW\TDH）可能支持，不绝对。

unix_timestamp()

也会返回一个时间戳，但并不是系统当前时间的时间戳。

1.2日期时间和时间戳转换

select unix_timestamp(to_date('2023-03-31'))-- 将日期格式转换为时间戳  【1680220800】,unix_timestamp(date('2023-03-31'))-- 将日期格式转换为时间戳  【1680220800】,unix_timestamp(current_timestamp())-- 将日期时间格式转换为时间戳 【1680371206】,from_unixtime(1680370963)-- 将时间戳转换为日期时间格式 【2023-04-01 17:42:43】,from_unixtime(1680370963,'yyyy/MM/dd HH:mm:ss')-- 将时间戳转换为日期时间格式 【2023/04/01 17:42:43】,from_unixtime(unix_timestamp('2023-04-01 17:42:43'),'yyyyMMdd')-- 将时间戳转换为日期  yyyyMMdd 格式 【20230401】,from_unixtime(unix_timestamp('2023-04-01 17:42:43'),'yyyy-MM')-- 将时间戳转换为日期  yyyy-MM 格式 【2023-04】,from_unixtime(unix_timestamp(current_timestamp()))-- 将时间戳转换为日期时间格式 【2023-04-01 17:46:46】,from_unixtime(cast(1680380654359/1000asint))-- 毫秒级的时间戳要使用/1000的方式 【2023-04-01 20:24:14】,date_format('2023-04-01 20:13:00','yyyyMMdd')-- 将日期或时间类型的数据转换为指定格式的字符串  【20230401】,date_format(CURRENT_TIMESTAMP(),'yyyy/MM/dd')-- 将日期或时间类型的数据转换为指定格式的字符串  【2023/04/01】;

特殊说明：to_char函数用于将日期或时间类型的数据转换为指定格式的字符串。hive中没有to_char()函数，但如果支持Orcale语法（腾讯TDW、星环TDH），则支持

to_char(date/timestamp, format)

其中，date/timestamp表示待转换的日期或时间类型数据，format表示转换后的字符串格式。
即使不支持orcale，也可以用

date_format(date/timestamp,format)

和

from_unixtime(unix_timestamp(date/timestamp), format)

来代替实现转换。

SELECT to_char(date('2023-04-01'),'YYYYMMDD'),to_char(date('2023-04-01 20:20:20'),'YYYY/MM/DD');

1.3日期时间部分提取

select to_date(current_timestamp())-- 将日期时间格式转换为日期格式 yyyy-MM-dd  【2023-04-01】,date(current_timestamp())-- 回日期时间中的日期部分，格式为yyyy-MM-dd  【2023-04-01】,year('2023-03-31 20:21:22')-- 返回日期时间中的年份，格式为yyyy 数值  【2023】,month('2023-03-31')-- 返回日期时间中的月份，格式为MM 数值 【4】,day('2023-03-31')-- day: 返回日期时间中的日，格式为dd 数值  【1】,hour('2023-03-31 20:21:22')-- hour: 返回日期时间中的小时，格式为hh 数值  【20】,minute('2023-03-31 20:21:22')-- minute: 返回日期时间中的分钟，格式为mm 数值  【21】,second('2023-03-31 20:21:22')-- second: 返回日期时间中的秒数，格式为ss 数值  【22】,month('20230331')-- 当无法识别日期时，返回 null  【NULL】,hour('2023-03-31')-- 当输入日期无时间时，返回数值0，默认为00:00:00  【0】,hour('青空')-- 非日期时间格式，返回 null  【NULL】,weekofyear('2023-01-08')--  返回日期时间在一年中的第几周  【1】'2023-01-08'是周日,weekofyear('2023-01-09')--  返回日期时间在一年中的第几周  【2】'2023-01-09'是周一;

当无法识别内容时，提取函数会返回NULL，而不是报错，这里需要注意，例如

hour('青空')

。

1.4日期计算函数

select add_months('2023-04-01',5)--  在日期上增加指定的月数 【2023-09-01】,add_months('2023-04-01',-5)--  在日期上增加指定的月数  【2022-11-01】,date_add('2023-04-01',5)--  在日期上增加指定的天数  【2023-04-06】,date_add('2023-04-01',-5)--  在日期上增加指定的天数  【2023-03-27】,date_sub('2023-04-01',5)--  在日期上减去指定的天数  【2023-03-27】,datediff('2023-04-01','2023-01-01')-- 计算两个日期之间的天数差  【90】,last_day('2023-04-05')-- 返回指定日期所在月份的最后一天  【2023-04-30】,date_add(last_day('2023-04-05'),1)-- 返回指定日期下个月的第一天  【2023-05-01】;

二.其他特殊计算记录

2.1计算时间上下5分钟的值

此方法用于计算5分钟访问量、通话量等问题的时间处理。

select 
    from_unixtime(floor(floor(unix_timestamp('2023-04-01 20:13:00','yyyy-MM-dd HH:mm:ss')/300)*300),'yyyy-MM-dd HH:mm:ss')-- 【2023-04-01 20:10:00】,from_unixtime(floor(floor(unix_timestamp('2023-04-01 20:18:00','yyyy-MM-dd HH:mm:ss')/300)*300),'yyyy-MM-dd HH:mm:ss')--【2023-04-01 20:15:00】,from_unixtime(floor(floor(unix_timestamp('2023-04-01 20:21:00','yyyy-MM-dd HH:mm:ss')/300)*300),'yyyy-MM-dd HH:mm:ss')--【2023-04-01 20:20:00】,from_unixtime(ceil(ceil(unix_timestamp('2023-04-01 20:13:00','yyyy-MM-dd HH:mm:ss')/300)*300),'yyyy-MM-dd HH:mm:ss')-- 【2023-04-01 20:15:00】,from_unixtime(ceil(ceil(unix_timestamp('2023-04-01 20:18:00','yyyy-MM-dd HH:mm:ss')/300)*300),'yyyy-MM-dd HH:mm:ss')-- 【2023-04-01 20:20:00】,from_unixtime(ceil(ceil(unix_timestamp('2023-04-01 20:21:00','yyyy-MM-dd HH:mm:ss')/300)*300),'yyyy-MM-dd HH:mm:ss')-- 【2023-04-01 20:25:00】,floor(date_format('2023-04-01 20:13:00','mm')/5)*5-- 【10】;

其中

floor()

和

ceil()

分别实现向下取整和向上取整操作。

2.2未完待续

声明：本文所载信息不保证准确性和完整性。文中所述内容和意见仅供参考，不构成实际商业建议，如有雷同纯属巧合。