对日期、时间的操作是 hive 上重要的一个操作，有一些细节总是容易混淆，在此整理记录一下。
TIMESTAMP
hive 中会有timestamp数据类型的数据，现在普遍存了 BIGINT 的类型，在之后转换的时候非常的麻烦。
DATE_FORMAT
日期 & 时间格式化 Hive wiki。
格式化属性
| Letter | Date or Time Component | Presentation | Examples |
|---|---|---|---|
| G | Era designator | Text | AD |
| y | Year | Year | 2019; 19 |
| Y | Week year | Year | 2019; 19 |
| M | Month in year | Month | January; Jan; 01 |
| w | Week in year | Number | 3 |
| W | Week in month | Number | 3 |
| D | Day in year | Number | 15 |
| d | Day in month | Number | 15 |
| F | Day of week in month | Number | 3 |
| E | Day name in week | Text | Tuesday; Tue |
| u | Day number of week (1 = Monday, …, 7 = Sunday) | Number | 2 |
| a | Am/pm marker | Text | PM |
| H | Hour in day (0-23) | Number | 23 |
| k | Hour in day (1-24) | Number | 23 |
| K | Hour in am/pm (0-11) | Number | 0 |
| h | Hour in am/pm (1-12) | Number | 12 |
| m | Minute in hour | Number | 00 |
| s | Second in minute | Number | 00 |
| S | Millisecond | Number | 0 |
| z | Time zone | General time zone | China Standard Time; CST; GMT+08:00 |
| Z | Time zone | RFC 822 time zone | +0800 |
| X | Time zone | ||
| Y | Year | Year | 2019 |
| YY | Year | Year | 20 |
| YYY | Year | Year | 2019 |
| YYYY | Year | Year | 2019 |
| M | Month in year | Month | 1 |
| MM | Month in year | Month | 01 |
| MMM | Month in year | Month | Jan |
| MMMM | Month in year | Month | January |
格式化例子
1
2
3
4
5
6
7
8
9
SET time='2019-01-15 23:00:00';
SELECT DATE_FORMAT(${hiveconf:time}, 'YYYY-MM-dd HH:mm:ss');
SELECT DATE_FORMAT(${hiveconf:time}, 'YY-MMMM-dd hh:mm:ss a Z');
--------------------------------------------------
2019-01-15 23:00:00
19-January-15 11:00:00 PM +0800
CURRENT_DATE
获取当前日期
-- 当前日期
SELECT CURRENT_DATE();
-- 昨天日期
SELECT DATE_FORMAT(DATE_SUB(CURRENT_DATE(), 1), 'yyyy-MM-dd')
--------------------------------------------------
2019-01-15
2019-01-14
TO_DATE
日期时间转日期函数
SET time='2019-01-15 01:02:03';
SELECT TO_DATE(${hiveconf:time});
--------------------------------------------------
2019-01-15
FROM_UNIXTIME
转化 unix 时间戳到当前时区的时间格式
1
2
3
4
select FROM_UNIXTIME(1547485323);
--------------------------------------------------
2019-01-15 01:02:03
UNIX_TIMESTAMP
获取当前时间 unix 时间戳
1
2
3
4
5
6
7
8
9
SET time='2019-01-15 01:02:03';
SELECT UNIX_TIMESTAMP();
SELECT UNIX_TIMESTAMP(${hiveconf:time});
--------------------------------------------------
1547606876
1547485323
YEAR & MONTH & DAY & HOUR & MINUTE & SECOND & WEEKOFYEAR
YEAR 返回日期中的年
MONTH 返回日期中的月
DAY 返回日期中的日
HOUR 返回日期中的时
MINUTE 返回日期中的分
SECOND 返回日期中的秒
WEEKOFYEAR 返回日期当前周
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SET time='2019-01-15 01:02:03';
SELECT CONCAT('日期中的年：', YEAR(${hiveconf:time}));
SELECT CONCAT('日期中的月：',MONTH(${hiveconf:time}));
SELECT CONCAT('日期中的日：',DAY(${hiveconf:time}));
SELECT CONCAT('日期中的时：',HOUR(${hiveconf:time}));
SELECT CONCAT('日期中的分：',MINUTE(${hiveconf:time}));
SELECT CONCAT('日期中的秒：',SECOND(${hiveconf:time}));
SELECT CONCAT('日期当前周：',WEEKOFYEAR(${hiveconf:time}));
--------------------------------------------------
日期中的年：2019
日期中的月：1
日期中的日：15
日期中的时：1
日期中的分：2
日期中的秒：3
日期当前周：3
DATE_ADD
返回日期后 n 天的日期
1
2
3
4
5
6
SET dt='2019-01-15';
SELECT DATE_ADD(${hiveconf:dt}, 7);
--------------------------------------------------
2019-01-22
DATE_SUB
返回日期前 n 天的日期
1
2
3
4
5
6
SET dt='2019-01-15';
SELECT DATE_SUB(${hiveconf:dt}, 7);
--------------------------------------------------
2019-01-08
DATEDIFF
计算开始日期减去结束日期的天数
1
2
3
4
5
6
SET dt='2019-01-15';
select DATEDIFF(${hiveconf:dt}, '2019-01-31');
--------------------------------------------------
-16
时间计算
上周一
1
2
3
4
5
6
SET dt='2019-01-15';
SELECT DATE_SUB(${hiveconf:dt}, CAST(DATE_FORMAT(${hiveconf:dt}, 'u') AS INT) + 6);
--------------------------------------------------
2019-01-07
本周一
1
2
3
4
5
6
SET dt='2019-01-15';
SELECT DATE_SUB(${hiveconf:dt}, CAST(DATE_FORMAT(${hiveconf:dt}, 'u') AS INT) - 1);
--------------------------------------------------
2019-01-14
下周一
1
2
3
4
5
6
SET dt='2019-01-15';
SELECT DATE_SUB(${hiveconf:dt}, CAST(DATE_FORMAT(${hiveconf:dt}, 'u') AS INT) - 8);
--------------------------------------------------
2019-01-21
上月初
1
2
3
4
5
6
SET dt='2019-01-15';
SELECT ADD_MONTHS(TRUNC(${hiveconf:dt}, 'MM'), -1);
--------------------------------------------------
2018-12-01
本月初
1
2
3
4
5
6
SET dt='2019-01-15';
SELECT TRUNC(${hiveconf:dt}, 'MM');
--------------------------------------------------
2019-01-01
下月初
1
2
3
4
5
6
SET dt='2019-01-15';
SELECT ADD_MONTHS(TRUNC(${hiveconf:dt}, 'MM'), 1);
--------------------------------------------------
2019-02-01
上月末
1
2
3
4
5
6
SET dt='2019-01-15';
SELECT DATE_SUB(TRUNC(${hiveconf:dt}, 'MM'), 1);
--------------------------------------------------
2018-12-31
本月末
1
2
3
4
5
6
SET dt='2019-01-15';
SELECT LAST_DAY(${hiveconf:dt});
--------------------------------------------------
2019-01-31
下月末
1
2
3
4
5
6
SET dt='2019-01-15';
SELECT DATE_SUB(ADD_MONTHS(TRUNC(${hiveconf:dt}, 'MM'), 2), 1);
--------------------------------------------------
2019-02-28