bigint hive 转化为日期 hive日期格式

Hive 常用的日期及时间函数，以及常用的场景介绍。

声明：

• 函数的参数中，中括号[]表示参数可选，即可不传入可传入；
• 时间是包括日期的，hive中的日期时间函数的对象可以是时间类型，也可以是字符串类型（STRING）；
• hive的日期函数，对时间(yyyy-MM-dd HH:mm:ss)格式的字符串几乎都是通用的。

/导图

一、常用日期时间

1.1 返回时间的函数

返回当前时间：

current_date() :

• 返回当前日期，“yyyy-MM-dd” 格式

current_timestamp() :

• 返回当前系统时间，“yyyy-MM-dd HH:mm:ss” 格式，精确到毫秒

unix 时间戳相关：

unix_timestamp([STRING timestamp [, STRING pattern]]) :

• 返回时间对应的 unix 时间戳（即返回距离"1970-01-01 00:00:00" 的秒数（之前的时间unix时间戳是负值），如果是中国时区，则以该天 8点为参照），是 int 型；
• 第一个参数是字符串类型的时间，第二个 pattern 用于匹配第一个参数时间字符串的格式，用于识别时间；
• 不传入参数则返回当前时间的 unix 时间戳，相当于 unix_timestamp(current_timestamp())；
• 只传入第 1 个参数的话，格式必须是 “yyyy-mm-dd HH:mm:ss” ，否则返回 NULL ，因为第二个参数 pattern 默认是这个格式。

from_timestamp(BIGINT unixtime [, STRING format]) :

• 将 unix 时间戳转换为指定格式的时间，可理解为 unix_timestamp 的逆过程；
• 第一个参数 unixtime 是 unix 时间戳，默认第二个参数 format 是 ‘yyyy-MM-dd HH:mm:ss’ 格式（即输出时间的格式）

1.2 时间格式化函数

转为日期形式：

to_date(STRING timestamp) : 字符串转日期

• 从时间格式的字符串中获取日期部分，“yyyy-MM-dd” 格式；
• 只要字符串前面是 “yyyy-MM-dd”，后面不是紧跟数字即可

转为自定义形式：

date_format(DATE|TIMESTAMP|STRING ts, STRING fmt)

• 将字符串或时间转为指定格式的时间；
• 第一个参数是必须是标准时间格式（即yyyy-MM-dd HH:mm:ss，可以是日期），第二个参数 fmt (format) 是想要输出的格式类型。

抽取时间中的部分内容：

以下都必须传入标准时间格式，返回的是 int 型：

year(STRING date): 返回年；

month: 返回月；

day: 返回天；

hour: 返回时；

minute: 返回分；

second: 返回秒。

1.3 时间计算函数

时间的加减：

date_add(DATE startdate, INT days) :

• 对时间按天做加减，返回的是日期；
• 第二个参数是 int型的，若为正则往后推，为负往前推。

add_months(DATE|STRING|TIMESTAMP start_date, INT num_months) :

• 对时间按月做加减，返回的是日期；
• 第二个参数是 int 型的，若为正则往后推，为负往前推。

返回差值：

datediff(STRING enddate, STRING startdate) :

• 计算两个时间相差的天数，返回的是 int型；
• 注意是前减后，即第一个参数 - 第二个参数；

months_between(DATE|TIMESTAMP|STRING enddate, DATE|TIMESTAMP|STRING startdate):

• 计算两个时间相差的月数，返回的是 浮点型；
• 注意是前减后。

1.4 时间转换函数

月初、月末、年初：

trunc(STRING date, STRING format) format: MONTH/MON/MM, YEAR/YYYY/YY

• 返回月初或年初的日期，格式是"yyyy-MM-dd"。

last_day(STRING date) :

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

当前日期的下一个 周几的日期：

next_day(STRING start_date, STRING day_of_week)

• 返回 当前日期下个 周几的 日期；如果当前日期是周一，day_of_week 参数输入的是‘MO’，则返回的是下周周一的日期；
• 第二个参数给周几的缩写（可以小写）或全称：下个周一（‘MO’）、下个周二（‘TU’）、下个周三（‘WE’）、下个周四（‘TH’）、下个周五（‘FR’）、下个周六（‘SA’）、下个周日（‘SU’）；
• 比如 next_day(‘2020-03-27’, ‘MO’) 返回’2020-03-27’（27号是周六）下个周一的日期，即’2020-03-30’。

更高维日期中的第几次：

weekofyear(STRING date) ：

• 返回时间在这一年中是第几周。

dayofmonth(STRING date)

• 返回时间在这一月中是第几天。

参考：https://ask.hellobi.com/blog/musper/16794

二、常用场景

2.1 判断某日期是周几

pmod(datediff(<需要判断的日期或时间>, '1970-01-04'), 7)
-- 返回值：int 类型；
-- 返回值范围：0~6
-- 如果是0，表示该日期是周日，若是1，则表示该日期为周一，其他以此类推

思路：以一个历史上是周日的时间为参照（这里是’1970-01-04’，是周日），然后用 datediff 函数实现需要判断的日期减去参照日期，最后用 pmod 函数取余数，该差值除以 7 余 多少，如果余0，表示需要判断的日期是周日，余1，表示是周一，其他以此类推。

2.2 给指定时间增加或减少一段时间

-- 返回指定时间8小时后的时间
cast(from_unixtime(unix_timestamp(<需要更改的时间>)+28800, 'yyyy-MM-dd HH:mm:ss') as string)

思路：从时间戳角度切入，通过 unix_timestamp 函数将时间转为时间戳，然后加上需要修改的时间，再用 from_unixtime 函数将时间戳转为想要的时间格式（yyyy-MM-dd HH:mm:ss），最后转为 string 格式。
因为时间戳的单位是秒，所以要增加8小时，就需要在原时间戳上边加上 28800秒（8 * 60 * 60 ）。

2.3 计算两个时间段相差时长

(unix_timestamp(<time1>) - unix_timestamp(<time2>) ) / 3600

思路：转为时间戳处理，除以3600（60 * 60）。

2.4 时间格式转换

-- 输出标准日期格式(注：<需要转化的日期或时间>本身需要是yyyy-MM-dd HH:mm:ss样式的)
to_date(<需要转化的日期或时间>)
date_format(<需要转化的日期或时间>, 'yyyy-MM-dd')
-- 2020-01-01 转为 20200101 样式
date_format(<需要转化的日期>, 'yyyyMMdd')

2.5 next_day 的具体应用（如本周一的日期，上周一的日期等）

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

2.6 trunc 的具体应用（）

trunc(add_months(current_timestamp(), -1), 'MM')  -- 上月1号