=Start=

缘由：

在用Hive SQL做数据分析的时候，一般少不了和日期时间函数打交道，但是我可能是用的还不够多、不够熟练，所以我每次要实现某个功能的时候还需要去现查，不是太方便，所以想着自己按照自己的习惯和需求做一个整理，方便后面忘了的时候快速参考。

在经过多次搜索之后发现了不少不错的文章，这里按照个人需求选取部分内容进行摘录，后面再有复杂需求的直接去官方文档中进行查询和组合实现。

正文：

参考解答：

几个前置说明项：

• Hive SQL中的 timestamp 类型数据显示格式是 2022-11-16 16:07:13.816 这样的；
• 而我们常说的时间戳是一个 bigint 类型的数字，通过 unix_timestamp() 可获取当前Unix时间戳（以秒为单位，10位数）；
• Hive SQL中 timestamp 类型的列是可以直接比较的，加减操作返回内容的格式也很友好，是时分秒的格式；而 unixtime 直接比较是相差的秒数，也可以但数值大了之后不直观，但可以按自己的需求做转换，其实也很灵活。

我的简单粗暴的记忆方式：

• unixtime 为 bigint 类型的Unix时间戳，所以 from_unixtime 函数用于将Unix时间戳转换为指定格式的字符串；
• timestamp 为 yyyy-MM-dd HH:mm:ss 格式的时间字符串，所以 unix_timestamp 函数用于接收日期时间字符串，并将其转换为Unix时间戳(以秒为单位)，如果失败返回null。

将日期字符串转换成unix时间戳

函数: unix_timestamp()
返回值类型: bigint
(hive 2.0 > version)通过 unix_timestamp() 函数获取，返回当前Unix时间戳(以秒为单位)。

函数: unix_timestamp(string date)
返回值类型: bigint
返回日期字符串 date （格式为 yyyy-MM-dd HH:mm:ss ）对应的Unix时间戳(以秒为单位)，若失败返回null。

select
unix_timestamp() as ts_bigint -- 返回当前时间戳 (1668586033)
,unix_timestamp('2022-11-16') as ts_bigint2 -- null 因为字符串格式不符合要求所以解析失败返回null
,unix_timestamp('2022-11-16 00:00:00') as ts_bigint3 -- 1668528000
,unix_timestamp('2022-11-16 18:31:36') as ts_bigint4 -- 1668594696

将unix时间戳对应的秒值转换为指定格式的字符串

函数： from_unixtime(bigint unixtime[, string format])
返回值：string
将unix时间戳对应的秒数转换为表示当前系统时区中该时刻的字符串表示形式，默认格式为“yyyy-MM-dd HH:mm:ss”。

select
from_unixtime(1668594696) -- '2022-11-16 18:31:36'
,from_unixtime(1668594696,'yyyy-MM-dd HH-mm-ss') -- '2022-11-16 18-31-36'

获取当前日期时间

函数: current_timestamp
返回值类型: timestamp (比如 2022-11-16 16:07:13.816)
返回查询计算开始时的当前时间戳(从Hive 1.2.0开始)。在同一个查询中对current_timestamp的所有调用都返回相同的值。(hive 2.0 <= version)建议使用 CURRENT_TIMESTAMP 常量进行获取当前系统时间。

函数: current_date
返回值类型: date
返回查询计算开始时的当前日期(从Hive 1.2.0开始)。在同一个查询中current_date的所有调用都返回相同的值。

select
current_timestamp() as ts_str -- 返回当前时间戳 (2022-11-16 16:07:13.816)

计算两个日期之间的天数

函数: datediff(string enddate, string startdate)
返回值类型: int
返回从 startdate 到 enddate 的天数，可以简单理解为前者减去后者(enddate-startdate)，或者说后者再经过几天会到前者。

select
datediff('2022-11-16', '2022-11-13') as day_range -- 3

日期字符串格式化

函数： date_format(date/timestamp/string ts, string fmt)
返回值：string
将日期/时间戳/字符串转换为由第2个参数 fmt 指定格式的字符串值（从 Hive 1.2.0 开始支持）。

select
date_format('2022-11-09','yyyy-MM-dd') -- '2022-11-09'
,date_format('2022-11-09','yyyy-MM-dd HH:mm:ss') -- '2022-11-09 00:00:00'
,date_format('2022-11-11 15:30:12.084','yyyy-MM-dd') -- '2022-11-11'

在特定日期上加减指定天数

date_add/date_sub 用于对指定日期增加/减去特定天数并获取结果日期。

函数： date_add(date/timestamp/string startdate, tinyint/smallint/int days)
返回值：
pre 2.1.0: string
2.1.0 on: date
给startdate添加天数，在Hive 2.1.0 (Hive -13248)之前，返回类型是String，因为当时创建这个方法时还没有Date类型存在。

select
date_add('2008-12-31', 1) -- '2009-01-01'
;

with t as (
  select '2020-12-28' as dt_start, '2021-01-12' as dt_end
)
select
idx, date_add(to_date(dt_start), idx) as dt
from t
lateral view posexplode(split(space(datediff(t.dt_end,t.dt_start)),' ')) pi as idx, empty
;

idx dt  
0 2020-12-28
1 2020-12-29
2 2020-12-30
3 2020-12-31
4 2021-01-01
...
13  2021-01-10
14  2021-01-11
15  2021-01-12

对 timestamp 类型的列进行比较

现在想计算2个日期之间的间隔有多大，在Hive SQL中该怎么处理？解决办法——直接比较就行，并且返回内容的格式也很友好，时分秒的格式。

share_timestamp TIMESTAMP #日期1
open_timestamp  TIMESTAMP #日期2

select
`share_timestamp`,
`open_timestamp`,
`open_timestamp`-`share_timestamp` as time_delay
;

2022-11-05 22:47:17.315 2022-11-05 22:47:28.712 11.397 seconds
2022-11-05 09:52:51.805 2022-11-05 09:53:25.856 34.051 seconds
2022-11-05 22:33:25.36  2022-11-05 23:04:50.45  31 minutes 25.09 seconds
2022-11-05 02:16:17.241 2022-11-05 14:26:08.578 12 hours 9 minutes 51.337 seconds

参考链接：

Hive SQL时间函数及用法
https://juejin.cn/post/7137207230713036831

Date Functions #官方文档，最权威也最及时
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-DateFunctions

Hive Date and Timestamp Functions | Examples
https://sparkbyexamples.com/apache-hive/hive-date-and-timestamp-functions-examples/

hive 中时间戳与时间字符串的相互转换
https://blog.csdn.net/HappyRocking/article/details/80854778

Introduction to Date Functions in Hive
https://www.educba.com/date-functions-in-hive/

Hive Date Functions
http://hadooptutorial.info/hive-date-functions/

Hive Functions Cheat-sheet, by Qubole
https://www.qubole.com/wp-content/uploads/2014/01/hive-function-cheat-sheet.pdf

How to Subtract TIMESTAMP-DATE-TIME in HIVE
https://sqlandhadoop.com/how-to-subtract-timestamp-date-time-in-hive/

=END=