format hive中的date date_format函数hive

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

• 一.常规计算函数说明

• 1.1系统当前日期时间
• 1.2日期时间和时间戳转换
• 1.3日期时间部分提取
• 1.4日期计算函数

• 二.其他特殊计算记录

• 2.1计算时间上下5分钟的值
• 2.2未完待续

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

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

一.常规计算函数说明

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

1.1系统当前日期时间

select current_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/1000 as int)) -- 毫秒级的时间戳要使用/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未完待续

---

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