Hive 中，可以用String、Date和Timestamp表示日期时间，String和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，但是字符串的插入的结果与字段类型相关。如上图所示，图中绿线表示插入成功，红线表示插入失败得到了不想要的结果。
一、相互转化
1.格式化 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
select date_format('2019-04-08', 'yyyy-MM-dd') -- 2019-04-08
注意：也可以用substr 方式截取年月或者substr+concat结合
2.字符串转时间 TO_DATE()
语法: to_date(string timestamp)
返回值: string
说明: 返回日期时间字段中的日期部分。
举例：
hive> select to_date('2019-02-16 14:02:03') from dual;
OK
2019-02-16
3.日期转年: year
语法: year(string date)
返回值: int
说明: 返回日期中的年。
举例：
hive> select year(’2019-12-08 10:03:01′) ;
2011
hive> select year(’2019-12-08′) ;
2012
4.日期转月: 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')
5.日期转周: weekofyear
语法: weekofyear (string date)
返回值: int
说明: 返回日期在当前的周数。
举例：
hive> select weekofyear('2019-12-08 10:03:01');
49
6.日期转天: day
语法: day (string date)
返回值: int
说明: 返回日期中的天。
举例：
hive> select day('2019-12-08 10:03:01') ;
8
hive> select day('2019-12-24');
24
7.日期转小时: hour
语法: hour (string date)
返回值: int
说明: 返回日期中的小时。
举例：
hive> select hour('2019-12-08 10:03:01') ;
10
8.日期转分钟: minute
语法: minute (string date)
返回值: int
说明: 返回日期中的分钟。
举例：
hive> select minute('2019-12-08 10:03:01') ;
3
9.日期转秒: 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')
四、获取上月同期
方式一：ADD_MONTHS()
-- 获取当前时间 2022-05-26
SELECT current_date();
-- 获取上月同一天 2022-04-26
SELECT add_months(current_date(),-1);
-- 获取上个月年月 2022-04
SELECT SUBSTR(add_months(current_date(),-1),1,7);
方式二：配凑
因为某些月份有30天有31天不能简单用date_sub减30天的方式来判度，下面以当前时间为2021-01-11
为例进行演示。
-- 获取上个月年月，格式一： 2020-12
select substr(date_sub(current_date(),day(current_date())),1,7)
-- 获取上个月年月，格式二： 202012
select concat(substr(date_sub(current_date(),day(current_date())),1,4),substr(date_sub(current_date(),day(current_date())),6,2))
-- 获取上个月同天
格式一：20201211
select concat(substr(date_sub(current_date(),day(current_date())),1,4),substr(date_sub(current_date(),day(current_date())),6,2),substr(current_date,9,2))
格式二：2020-12-11
select concat(substr(date_sub(current_date(),day(current_date())),1,4),'-',substr(date_sub(current_date(),day(current_date())),6,2),'-',substr(current_date,9,2))
五、时间拼接和格式更改
方法1: from_unixtime+ unix_timestamp
--20171205转成2017-12-05
select from_unixtime(unix_timestamp('20171205','yyyyMMdd'),'yyyy-mm-dd');
--2017-12-05转成20171205
select from_unixtime(unix_timestamp('2017-12-05','yyyy-MM-dd'),'yyyyMMdd') ;
方法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;
六、计算时间差
1.计算相差月份
-- 情况一：月份不同，但是日相同
select months_between('2020-03-28','2020-02-28')
1
-- 情况二：月份不同，日不同（默认显示小数）
select months_between('2020-03-28','2020-02-10')
1.58064516
-- 情况三：月份不同，日不同（显示整数）
-- 思路：拼接成每个月1号或者其他号，但要保证前后为同一个日2020-05-10-->2021-05-01
select months_between(concat(substr('2020-05-10',1,7),'-01'),concat(substr('2020-03-25',1,7),'-01'))
2
-- 思路2：格式化操作20210510-->20210501
select months_between(
from_unixtime(unix_timestamp(substr('20200510',1,6),'yyyyMM'),'yyyy-MM-dd'),
from_unixtime(unix_timestamp(substr('20200325',1,6),'yyyyMM'),'yyyy-MM-dd'))
2
备忘录
1.datetime格式日期
select
day -- 时间
,date_add(day,1 - dayofweek(day)) as week_first_day -- 本周第一天_周日
,date_add(day,7 - dayofweek(day)) as week_last_day -- 本周最后一天_周六
,date_add(day,1 - case when dayofweek(day) = 1 then 7 else dayofweek(day) - 1 end) as week_first_day -- 本周第一天_周一
,date_add(day,7 - case when dayofweek(day) = 1 then 7 else dayofweek(day) - 1 end) as week_last_day -- 本周最后一天_周日
,next_day(day,'TU') as next_tuesday -- 当前日期的下个周二
,trunc(day,'MM') as month_first_day -- 当月第一天
,last_day(day) as month_last_day -- 当月最后一天
,to_date(concat(year(day),'-',lpad(ceil(month(day)/3) * 3 -2,2,0),'-01')) as season_first_day -- 当季第一天
,last_day(to_date(concat(year(day),'-',lpad(ceil(month(day)/3) * 3,2,0),'-01'))) as season_last_day -- 当季最后一天
,trunc(day,'YY') as year_first_day -- 当年第一天
,last_day(add_months(trunc(day,'YY'),12)) as year_last_day -- 当年最后一天
,weekofyear(day) as weekofyear -- 当年第几周
,second(day) as second -- 秒钟
,minute(day) as minute -- 分钟
,hour(day) as hour -- 小时
,day(day) as day -- 日期
,month(day) as month -- 月份
,lpad(ceil(month(day)/3),2,0) as season -- 季度
,year(day) as year -- 年份
from (
select '2018-01-02 01:01:01' as day union all
select '2018-02-02 02:03:04' as day union all
select '2018-03-02 03:05:07' as day union all
select '2018-04-02 04:07:10' as day union all
select '2018-05-02 05:09:13' as day union all
select '2018-06-02 06:11:16' as day union all
select '2018-07-02 07:13:19' as day union all
select '2018-08-02 08:15:22' as day union all
select '2018-09-02 09:17:25' as day union all
select '2018-10-02 10:19:28' as day union all
select '2018-11-02 11:21:31' as day union all
select '2018-12-02 12:23:34' as day
) t1
;
扫一扫
2568
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
