hive日期函数

Hive 中，可以用String、Date和Timestamp表示日期时间，String 用 yyyy-MM-dd 的形式表示，Date 用 yyyy-MM-dd 的形式表示，Timestamp 用 yyyy-MM-dd hh:mm:ss 的形式表示。这三种数据类型在使用细节上，有一些需要注意的点：

          

Join比较
在两表Join时，会涉及到字段的比较，此时应注意：

如第一张图所示，如果时间信息中不包含时分秒，String 与 Date、Timestamp 表达的时间相同时是可以直接比较的，但是Date和Timestamp之间却不能直接比较的。
如果想比较这两种时间类型，需要用cast函数做转换，如：a_table join b_table on (a_table.timestamp_column = cast(b_table.date_column as timestamp));
如第二张图所示，如果时间信息中包含时分秒，此时String 与 Timestamp 表达的时间相同时是可以直接比较的，Date 不能表示带时分秒的信息。
Insert value
在insert value时，使用者一般用字符串的形式向Hive表中插入value，但是字符串的插入的结果与字段类型相关。如上图所示，图中绿线表示插入成功，红线表示插入失败得到了不想要的结果。

一、相互转化

格式化 date_format

select date_format('2019-04-08', 'yyyy') --得到：2019
select date_format('2019-04-08', 'yyyy-MM') --得到：2019-04
select date_format('2019-04-08', 'yy-MM') --得到：19-04

注意：也可以用substr 方式截取年月或者substr+concat结合

字符串转时间 to_date

语法: to_date(string timestamp)
返回值: string
说明: 返回日期时间字段中的日期部分。
举例：

hive> select to_date('2019-02-16 14:02:03') from dual;
OK
2019-02-16

日期转年函数: year

语法: year(string date)

返回值: int

说明: 返回日期中的年。

举例：
hive> select year(’2019-12-08 10:03:01′) ;
2011

hive> select year(’2019-12-08′) ;
2012

日期转月函数: month

语法: month (string date)

返回值: int

说明: 返回日期中的月份。

举例：
hive> select month(’2019-12-08 10:03:01′);
12

hive> select month(’2019-08-08′);
8

【注意】这里的转成月份是0-12的数字显示不出年份，要输出‘2019-12’这种格式，需要用到时间戳函数

select from_unixtime( unix_timestamp(字段名), 'yyyy-MM' ) from 表名

例如

-- 获取当前时间戳（1565858389）
select unix_timestamp()   

--获取当前日期和时间（2019-11-13 17:18:55）
select from_unixtime(unix_timestamp()) 

--获取当前日期和时间，精确到毫秒（2019-11-13 17:18:55.720）
select current_timestamp()

-- 获取当前日期（2019-11-13）
 select from_unixtime(unix_timestamp(),'yyyy-MM-dd')
 
--  获取当前年份（2019）
 select from_unixtime(unix_timestamp(),'yyyy')
 
 -- 获取当前月份（0-12的数字）
  select from_unixtime(unix_timestamp(),'MM')

日期转周函数: weekofyear

语法: weekofyear (string date)

返回值: int

说明: 返回日期在当前的周数。

举例：

hive> select weekofyear(’2019-12-08 10:03:01′);
49

日期转天函数: day

语法: day (string date)

返回值: int

说明: 返回日期中的天。

举例：

hive> select day(’2019-12-08 10:03:01′) ;
8

hive> select day(’2019-12-24′);
24

日期转小时函数: hour

语法: hour (string date)

返回值: int

说明: 返回日期中的小时。

举例：

hive> select hour(’2019-12-08 10:03:01′) ;
10

日期转分钟函数: minute

语法: minute (string date)

返回值: int

说明: 返回日期中的分钟。

举例：

hive> select minute(’2019-12-08 10:03:01′) ;
3

日期转秒函数: second

语法: second (string date)

返回值: int

说明: 返回日期中的秒。

举例：

hive> select second(’2019-12-08 10:03:01′);
1

二、时间戳和日期格式互转

1. 日期>>>>时间戳

（1）unix_timestamp() 获取当前时间戳

例如：select unix_timestamp()   --1565858389

注意事项：

(a) unix_timestamp(string timestamp) 输入的时间戳格式必须为’yyyy-MM-dd HH:mm:ss’,如不符合则返回null

例如：

select unix_timestamp('2019-08-15 16:40:00')   --1565858400
select unix_timestamp('2019-08-15')  --null

(b)unix_timestamp(string date,string pattern) 将指定时间字符串格式字符串转化成unix时间戳,如不符合则返回null

例如：

select unix_timestamp('2019-08-15','yyyy-MM-dd')   --1565798400

select unix_timestamp('2019-08-15 16:40:00','yyyy-MM-dd HH:mm:ss')   --1565858400

select unix_timestamp('2019-08-15','yyyy-MM-dd HH:mm:ss')   --null

2.时间戳>>>>日期

（1）普通格式
from_unixtime(bigint unixtime,string format) 将时间戳秒数转化为UTC时间，并用字符串表示，指定输出的时间格式，其中unixtime 是10位的时间戳值，而13位的所谓毫秒的是不可以的。

例如：

--2019-08-15 16:39:49
select from_unixtime(1565858389,'yyyy-MM-dd HH:mm:ss')  

--2019-08-15
select from_unixtime(1565858389,'yyyy-MM-dd') 

（2）特殊格式:先转10位

如果unixtime为13位的，需要先转成10位

--2019-03-22 00:00:00
select from_unixtime(cast(1553184000488/1000 as int),'yyyy-MM-dd HH:mm:ss')

--2019-03-22 00:00:00 
select from_unixtime(cast(substr(1553184000488,1,10) as int),'yyyy-MM-dd HH:mm:ss')  

三、获取当前日期和时间

语法: from_unixtime( unixtime,“string format”)后面的年月日格式可以根据需要自己设置
或者用current_timestamp()和 current_date()

举例：

--1. 获取当前日期和时间（年月日时分秒）
--写法一：
select from_unixtime(unix_timestamp(),"yyyy-MM-dd HH:mm:ss")
2020-04-21 11:02:55
--写法二：
select substr(current_timestamp(),1,19)
2020-04-21 11:02:55


-- 2.获取当前日期
--写法一：
select from_unixtime(unix_timestamp(),"yyyy-MM-dd")
2020-04-21
--写法二：（推荐）
select current_date()或者select current_date
2020-04-21
-- 写法三：
select substr(current_timestamp(),1,10)
2020-04-21

--3.  获取当前年份（2020）
 select from_unixtime(unix_timestamp(),'yyyy')
 2020
 -- 获取当前月份（0-12的数字）
 select from_unixtime(unix_timestamp(),'MM')

四、时间拼接和更改格式

方法1: from_unixtime+ unix_timestamp
--20171205转成2017-12-05 
select from_unixtime(unix_timestamp('20171205','yyyymmdd'),'yyyy-mm-dd') from dual;

--2017-12-05转成20171205
select from_unixtime(unix_timestamp('2017-12-05','yyyy-mm-dd'),'yyyymmdd') from dual;

方法2: substr + concat
--20171205转成2017-12-05 
select concat(substr('20171205',1,4),'-',substr('20171205',5,2),'-',
substr('20171205',7,2)) from dual;

--2017-12-05转成20171205
select concat(substr('2017-12-05',1,4),substr('2017-12-05',6,2),
substr('2017-12-05',9,2)) from dual;

已知日期	要求日期	语句	结果
本周任意一天	本周一	select date_sub(next_day('2016-11-29','MO'),7) ;	2016-11-28
本周任意一天	上周一	select date_sub(next_day('2016-11-29','MO'),14) ;	2016-11-21
本周任意一天	本周二	select date_sub(next_day('2016-11-29','MO'),6)	2016-11-29
本周任意一天	上周二	select date_sub(next_day('2016-11-29','MO'),13) ;	2016-11-22
本周任意一天	上周末	select date_sub(next_day('2016-11-29','MO'),8) ;	2016-11-27
本月任意一天	上月末	select date_sub(trunc('2016-11-02','MM'),1);	2016-10-31
本月任意一天	上月初	select trunc(add_months('2016-11-02',-1),'MM')	2016-10-01
本月任意一天	本月初	select trunc('2016-11-02','MM')	2016-11-01
本月任意一天	上上月26	select date_add(add_months(trunc('2016-11-02','MM'),-2),25) ;	2016-09-26
本月任意一天	上月26	select date_add(add_months(trunc('2016-11-02','MM'),-1),25) ;	2016-10-26
	当前时间戳	select current_timestamp() ;	2016-11-30 15:18:06.276
	当前时间	select current_date() ;	2016-11-30
本季度任意一天	上季度初	case quarter('2016-05-23')     when 1 then concat(year('2016-05-23')-1,'-10-01')     when 2 then concat(year('2016-05-23'),'-01-01')     when 3 then concat(year('2016-05-23'),'-04-01')     when 4 then concat(year('2016-05-23'),'-07-01')  end 或 add_months(concat(year('2017-02-23'),'-',substr(concat('0',quarter('2017-02-23')*3+1),-2),'-01'),-6)
本季度任意一天	本季度初	case quarter('2016-05-23')     when 1 then concat(year('2016-05-23'),'-01-01')     when 2 then concat(year('2016-05-23'),'-04-01')     when 3 then concat(year('2016-05-23'),'-07-01')     when 4 then concat(year('2016-05-23'),'-10-01') end 或 add_months(concat(year('2017-02-23'),'-',substr(concat('0',quarter('2017-02-23')*3+1),-2),'-01'),-3)