常用的hive日期函数

数据处理

以下日期函数均基于hive 1.1.0-cdh5.7.1版本。

from_utc_timestamp：将UTC时间转换成指定时区
参数类型：timestamp或者timestamp格式的string
返回值类型：string

1
2
SELECT FROM_UTC_TIMESTAMP(min_deal_time, 'GMT+8') FROM pubdate; -- min_deal_time TIMESTAMP
SELECT FROM_UTC_TIMESTAMP('2016-11-10 16:59:56.0', 'GMT+8'); -- 2016-11-10 16:59:56.0 timstamp格式的string

to_utc_timestamp：将某一时区下的时间转换成UTC时间
参数类型：timestamp或者timestamp格式的string
返回值类型：string

1
2
SELECT TO_UTC_TIMESTAMP(min_comment_time, 'GMT+8') FROM pubdate; -- min_comment_time TIMESTAMP
SELECT TO_UTC_TIMESTAMP('2016-11-10 16:59:56.0', 'GMT+8'); -- 2016-11-10 16:59:56.0 timstamp格式的string

to_date：将日期timestamp格式的string或者timestamp转换成日期
参数类型：timestamp或者timestamp格式的string
返回值类型：string

1
2
SELECT TO_DATE(start_time) FROM shop_promotion; -- min_comment_time TIMESTAMP
SELECT TO_DATE('2016-11-02 10:10:00.0'); -- 返回 '2016-11-02'

year：从日期中返回年份部分
参数类型：string格式的date，timestamp格式的string, 或者timestamp格式的日期
返回值类型：int

1
2
3
SELECT YEAR(start_time) FROM shop_promotion LIMIT 100; -- start_time TIMESTAMP
SELECT YEAR('2016-11-02 10:10:00.0'); -- 返回 2016
SELECT YEAR('2016-12-12'); -- 返回 2016

month：从日期中返回月份部分
参数类型：string格式的date，timestamp格式的string, 或者timestamp格式的日期
返回值类型：int

1
2
3
SELECT MONTH(end_time) FROM shop_promotion LIMIT 100; -- end_time TIMESTAMP
SELECT MONTH('2016-11-02 10:10:00.0'); -- 返回 11
SELECT MONTH('2016-12-12'); -- 返回 12

date：从字符串中返回日期部分
参数类型：timestamp格式的时间，不接受timestamp格式的string
返回值类型：string

1
SELECT DATE(min_comment_time) FROM pubdate_origin;

day：从日期中返回天的部分
参数类型：string格式的date，timestamp格式的string, 或者timestamp格式的日期
返回值类型：int

1
2
3
SELECT DAY(end_time) FROM shop_promotion LIMIT 100;
SELECT DAY('2016-11-02 10:10:00.0'); -- 返回 11
SELECT DAY('2016-12-12'); -- 返回 12

date_sub：返回开始日期start_date减少days天后的日期
参数类型：timestamp格式的时间或者string格式下的timestamp（date）| 整型
返回值类型：string

1
2
3
SELECT DATE_SUB(FROM_UTC_TIMESTAMP(min_deal_time, 'GTM+8'), 1)) FROM pubdate;
SELECT DATE_SUB('2016-11-02 10:10:00.0', 1); -- 返回 2016-11-01
SELECT DATE_SUB('2016-11-02', 2); -- 返回 2016-10-31

date_add：返回开始日期start_date增加days天后的日期
参数类型：timestamp格式的时间或者string格式下的timestamp（date）| 整型
返回值类型：string

1
2
3
SELECT DATE_ADD(FROM_UTC_TIMESTAMP(min_deal_time, 'GTM+8'), 1)) FROM pubdate;
SELECT DATE_ADD('2016-11-02 10:10:00.0', 1); -- 返回 2016-11-03
SELECT DATE_ADD('2016-11-02', 2); -- 返回 2016-11-04

trunc：日期时间截断函数
参数类型：timestamp或者string，还有自定义参数负责指定截断部分；

  • SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y: 年份
  • Q: 季度
  • MONTH, MON, MM, RM: 月份
  • WW, W: Same day of the week as the first day of the month.
  • DDD, DD, J: 转换成timestamp
  • DAY, DY, D: 转换成当前日期所在星期的第一天的日期
  • HH, HH12, HH24: 转换成timestamp，包含（24小时制）小时
  • MI: 分钟
    返回值类型：string
1
2
3
4
5
6
-- 根据每周的第一天的日期和商品ID对每日销量进行汇总，求得商品的周销量
SELECT product_id, SUM(count) AS weekly_deals, TO_DATE(TRUNC(dt, 'D')) AS week
FROM daily_deal_wide
WHERE pubdate >= '2016-10-11' AND pubdate <= '2016-11-11' AND category_name = '裤子'
GROUP BY week, product_id
HAVING weekly_deals >= 50
|