Hive日期格式转换用法和教训

朱雀 2022-05-27 06:47 172阅读 0赞

本文将从以下两个方面介绍：

> 一、关于hive日期格式-亲身经历的教训(莫名其妙)：
> 
> 二、具体各种hive日期函数用法：

**写在前面：**

> Hive日期格式以下写法是错误的：  
> 'yyyy-mm-dd'  
> 'yyyymmdd'  
> **一定要牢记,为避免和分钟格式(mm)冲突，月份的格式一定要写为(MM)，否则将会出现莫名其妙的结果！！**

### **一、关于hive日期格式-亲身经历的教训(莫名其妙)：** ###

> 错误示例一、  虽然将月份写成了mm，但是却出现了和预期一样的结果  
> select from\_unixtime(unix\_timestamp('2018-10-03','yyyy-mm-dd'),'yyyymmdd');  
> 18/07/05 16:26:48 INFO SparkHiveShell: current SQL: select from\_unixtime(unix\_timestamp('2018-10-03','yyyy-mm-dd'),'yyyymmdd')  
> 20181003
> 
> 错误示例二、 这次将月份写成了mm，不幸的是，出现的结果显然不符合预期，尤其在复杂sql中，尤其要牢记：分钟格式写成(mm)，月份的格式一定要写为(MM)  
> select to\_date(tb\_a.income\_time) income\_time, from\_unixtime(unix\_timestamp(to\_date(tb\_a.income\_time),'yyyy-mm-dd'),'yyyymmdd'),tb\_a.product\_id,sum(tb\_a.expect\_income)/100 as ratio  
> from  
>     (select user\_id,pre\_orderid,expect\_income,product\_id,income\_time from hivet\_partner\_settle\_new  
>     where product\_id in ('S155607425','S154598293') and income\_time>=from\_unixtime(unix\_timestamp('20180628','yyyymmdd'),'yyyy-mm-dd') and income\_time<=from\_unixtime(unix\_timestamp('20180630','yyyymmdd'),'yyyy-mm-dd') and pre\_orderid!='' and pday>='20180628\_inc' and pday<='20180630\_inc'  
>     ) tb\_a  
>  join  
>      (  
>     select product\_id,pday from hivet\_product\_conf  
>     where type=1 and pday>='20180628' and pday<='20180630' and get\_json\_object(\`conf\`,'$.is\_data\_return')='10'  
>     ) tb\_b on tb\_a.product\_id=tb\_b.product\_id and tb\_b.pday=from\_unixtime(unix\_timestamp(tb\_a.income\_time,'yyyy-mm-dd'),'yyyymmdd')  
> group by to\_date(tb\_a.income\_time),tb\_a.product\_id;
> 
> 2018-06-28  20180028    S155607425  51606.0                           
> 2018-06-29    20180029    S155607425    48690.0  
> 2018-06-28    20180028    S154598293    14400.0  
> 2018-06-29    20180029    S154598293    14400.0
> 
> 错误示例二的正确格式如下：   
> select to\_date(tb\_a.income\_time) income\_time, from\_unixtime(unix\_timestamp(to\_date(tb\_a.income\_time),'yyyy-MM-dd'),'yyyyMMdd'),tb\_a.product\_id,sum(tb\_a.expect\_income)/100 as ratio  
> from  
>     (select user\_id,pre\_orderid,expect\_income,product\_id,income\_time from hivet\_partner\_settle\_new  
>         -- 回传收入 T-2  增量，  0610 0620  
>     --（hivet\_partner\_settle\_new延迟一天半）  
>     where product\_id in ('S154598293','S155607425') and income\_time>=from\_unixtime(unix\_timestamp('20180628','yyyymmdd'),'yyyy-mm-dd') and income\_time<=from\_unixtime(unix\_timestamp('20180630','yyyymmdd'),'yyyy-mm-dd') and pre\_orderid!='' and pday>='20180628\_inc' and pday<='20180630\_inc'  
>     ) tb\_a  
>  join  
>      (  
>     select product\_id,pday from hivet\_product\_conf  
>     where type=1 and pday>='20180628' and pday<='20180630' and get\_json\_object(\`conf\`,'$.is\_data\_return')='10'  
>     ) tb\_b on tb\_a.product\_id=tb\_b.product\_id and tb\_b.pday=from\_unixtime(unix\_timestamp(tb\_a.income\_time,'yyyy-mm-dd'),'yyyymmdd')  
> group by to\_date(tb\_a.income\_time),tb\_a.product\_id;  
> 2018-06-28  20180628     S155607425   51606.0                           
> 2018-06-29    20180629    S155607425    48690.0  
> 2018-06-28    20180628    S154598293    14400.0  
> 2018-06-29    20180629    S154598293    14400.0

### **二、具体各种hive日期函数用法：** ###

**1.日期函数UNIX时间戳转日期函数:** from\_unixtime语法:from\_unixtime(bigint unixtime\[, stringformat\])   
返回值: string  
说明: 转化UNIX时间戳（从1970-01-0100:00:00 UTC到指定时间的秒数）到当前时区的时间格式  
举例：  
hive> select from\_unixtime(1323308943,'yyyyMMdd') from dual;  
20111208

**2.获取当前UNIX时间戳函数: **unix\_timestamp语法:   unix\_timestamp()   
返回值:   bigint  
说明: 获得当前时区的UNIX时间戳  
举例：  
hive>   select unix\_timestamp() from dual;  
1323309615

**3.日期转UNIX时间戳函数: **unix\_timestamp语法:unix\_timestamp(string date)   
返回值:   bigint  
说明: 转换格式为“yyyy-MM\-dd HH:mm:ss“的日期到UNIX时间戳。如果转化失败，则返回0。  
举例：  
hive>   select unix\_timestamp('2011-12-07 13:01:03') from dual;  
1323234063

**4.指定格式日期转UNIX时间戳函数:** unix\_timestamp语法:   unix\_timestamp(string date,string pattern)   
返回值:   bigint  
说明: 转换pattern格式的日期到UNIX时间戳。如果转化失败，则返回0。  
举例：  
hive>   select unix\_timestamp('20111207 13:01:03','yyyyMMddHH:mm:ss') from dual;  
1323234063

**5.日期时间转日期函数:** to\_date语法:   to\_date(string timestamp)   
返回值:   string  
说明: 返回日期时间字段中的日期部分。  
举例：  
hive>   select to\_date('2011-12-08 10:03:01') from dual;  
2011-12-08

**6.日期转年函数: year语法: **  year(string date)   
返回值: int  
说明: 返回日期中的年。  
举例：  
hive>   select year('2011-12-08 10:03:01') from dual;  
2011  
hive>   select year('2012-12-08') fromdual;  
2012

**7.日期转月函数: **month语法: month   (string date)   
返回值: int  
说明: 返回日期中的月份。  
举例：  
hive>   select month('2011-12-08 10:03:01') from dual;  
12  
hive>   select month('2011-08-08') fromdual;  
8

**8.日期转天函数: **day语法: day   (string date)   
返回值: int  
说明: 返回日期中的天。  
举例：  
hive>   select day('2011-12-08 10:03:01') from dual;  
8  
hive>   select day('2011-12-24') fromdual;  
24

**9.日期转小时函数:** hour语法: hour   (string date)   
返回值: int  
说明: 返回日期中的小时。  
举例：  
hive>   select hour('2011-12-08 10:03:01') from dual;  
10

**10.日期转分钟函数:** minute语法: minute   (string date)   
返回值: int  
说明: 返回日期中的分钟。  
举例：  
hive>   select minute('2011-12-08 10:03:01') from dual;  
3

**11.日期转秒函数:** second语法: second   (string date)   
返回值: int  
说明: 返回日期中的秒。  
举例：  
hive>   select second('2011-12-08 10:03:01') from dual;  
1

**12.日期转周函数: **weekofyear语法:   weekofyear (string date)   
返回值: int  
说明: 返回日期在当前的周数。  
举例：  
hive>   select weekofyear('2011-12-08 10:03:01') from dual;  
49

**13.日期比较函数: **datediff语法:   datediff(string enddate,string startdate)   
返回值: int  
说明: 返回结束日期减去开始日期的天数。  
举例：  
hive>   select datediff('2012-12-08','2012-05-09')from dual;  
213

**14.日期增加函数: **date\_add语法:   date\_add(string startdate, intdays)   
返回值: string  
说明: 返回开始日期startdate增加days天后的日期。  
举例：  
hive>   select date\_add('2012-12-08',10)from dual;  
2012-12-18

**15.日期减少函数: **date\_sub语法:   date\_sub (string startdate,int days)   
返回值: string  
说明: 返回开始日期startdate减少days天后的日期。  
举例：  
hive>   select date\_sub('2012-12-08',10)from dual;  
2012-11-28

参考网址：https://blog.csdn.net/lichangzai/article/details/19406215