Hive Spark 时间函数

hive的常用时间函数主要包括：current_date，current_timestamp，to_date，from_unixtime，unix_timestamp，dayofmonth，year，month，day，hour，minute，second，datediff，date_sub，date_add，add_months，本文会列出各个函数的使用示例以供后续查询使用，除hive外，spark-sql也是适用的。

获取当前日期对象

select current_date();
-- 2023-06-18  date对象


select current_timestamp();
-- 2023-06-18 14:05:16.892   timestamp对象

bigint时间戳转string时间

秒级别时间戳转换字符串时间

SELECT FROM_UNIXTIME(1687063220, 'yyyy-MM-dd');
-- 2023-06-18

SELECT FROM_UNIXTIME(1687063220, 'yyyy-MM-dd HH:mm:ss');
-- 2023-06-18 04:40:20

string时间转bigint时间戳

字符串时间转换秒级别时间戳

SELECT UNIX_TIMESTAMP('2023-06-18 04:40:20', 'yyyy-MM-dd HH:mm:ss');
-- 1687063220

SELECT UNIX_TIMESTAMP('2023-06-18', 'yyyy-MM-dd');
-- 1687046400

    

带时区的字符串

-- 解析带时区的字符串 => 秒级别的时间戳
SELECT unix_timestamp('11/Jul/2023:23:01:50 +0800', 'dd/MMM/yyyy:HH:mm:ss +0800') as a
-- 1689084110


-- 解析带时区的字符串 => 秒级别的时间戳 => 不带时间戳的字符串
SELECT from_unixtime(
    unix_timestamp('11/Jul/2023:23:01:50 +0800', 'dd/MMM/yyyy:HH:mm:ss +0800'),
    'yyyy-MM-dd HH:mm:ss'
    ) as a
-- 2023-07-11 23:01:50


-- 注意：如果月份是英文的形式用 MMM，是数字的形式用 MM

日期时间截取

SELECT SPLIT('2023-12-08 16:03:22', ' ')[0]; -- 2023-12-08
SELECT SPLIT('2023-12-08 16:03:22', ' ')[1]; -- 16:03:22


SELECT substr('2023-12-08 16:03:22',  1, 10); -- 2023-12-08
SELECT substr('2023-12-08 16:03:22', 12, 20); -- 16:03:22


-- hive 会自动转换 时间对象 和 字符串的时间
SELECT TO_DATE('2023-12-08') = '2023-12-08';  -- true
SELECT TO_DATE('2023-12-08 16:03:22') = '2023-12-08 16:03:22'; -- true

年月日时分秒

返回值为int

select YEAR('2021-04-19 12:04:50') -- 2021
select MONTH('2021-04-19 12:04:50') -- 4
select DAY('2021-04-19 12:04:50') -- 19
select HOUR('2021-04-19 12:04:50') -- 12
select MINUTE('2021-04-19 12:04:50') -- 4
select SECOND('2021-04-19 12:04:50') -- 50

select YEAR('2021-04-19') -- 2021
select MONTH('2021-04-19') -- 4
select DAY('2021-04-19') -- 19
select HOUR('2021-04-19') -- 0
select MINUTE('2021-04-19') -- 0
select SECOND('2021-04-19') -- 0

select dayofmonth('2023-06-18 13:32:04') -- 18

计算日期是星期几

-- 输出 date 对应星期几
SELECT PMOD(DATEDIFF('2021-04-18', '1900-01-08'), 7) + 1 AS week_day

-- 输出 date 上周周日
SELECT DATE_SUB('2021-04-18', PMOD(DATEDIFF('2021-04-18', '1900-01-08'), 7) + 1) AS Last_Sunday

-- 输出 date 上周周一
SELECT DATE_SUB('2021-04-18', PMOD(DATEDIFF('2021-04-18', '1900-01-08'), 7) + 7) AS Last_Monday;

-- 输出 date 所在周的周一
SELECT DATE_SUB('2021-04-18', PMOD(DATEDIFF('2021-04-18', '1900-01-08'), 7)) AS Monday_Date;


select to_date(date_sub(next_day('2023-06-18', 'monday'), 14)) -- 上周一
select to_date(date_sub(next_day('2023-06-18', 'monday'), 8))  -- 上周日

-- 输出 上个月的当前天所在周几
select pmod(datediff(months_sub('2023-06-18',1),'1900-01-08'),7)+1

-- 输出 上个月的当前天所在周的周一
select to_date(date_sub(months_sub('2023-06-18',1), pmod(datediff(months_sub('2023-06-18',1),'1900-01-08'),7)+1-1))

-- 输出 上个月的当前天所在周的周日
select to_date(date_sub(months_sub('2023-06-18',1), pmod(datediff(months_sub('2023-06-18',1),'1900-01-08'),7)+1-1-6))

日期时间间隔计算

• 日期间隔计算 datediff，只能精确到天

-- 前面日期减去后面日期
SELECT DATEDIFF('2021-03-02', '2021-02-28') --  2
SELECT DATEDIFF('2021-02-28', '2021-03-02') -- -2
SELECT DATEDIFF('2021-03-10', '2021-03-01') --  9

-- 时间部分会被丢弃
SELECT DATEDIFF('2021-02-28 12:12:12', '2021-03-02 12:12:12') -- -2
SELECT DATEDIFF('2021-03-02 12:12:12', '2021-02-28 12:12:12') --  2

• 时间间隔计算：自定义精确到小时、分钟、天等

-- 两个时间相差多少 天
select (unix_timestamp('2023-06-19 13:21:31') - unix_timestamp('2023-06-18 13:21:39'))/86400

-- 两个时间相差多少 小时
select (unix_timestamp('2023-06-19 13:21:31') - unix_timestamp('2023-06-18 13:21:39'))/3600

-- 两个时间相差多少 分钟
select (unix_timestamp('2023-06-19 13:21:35') - unix_timestamp('2023-06-18 13:21:42'))/60

日期增加减少

-- 2021-04-29 时间部分被丢弃
SELECT DATE_ADD('2021-04-19 12:12:12', 10);

SELECT DATE_ADD('2021-04-19', 10)  -- 2021-04-29
SELECT DATE_ADD('2021-04-19', -10) -- 2021-04-09

SELECT DATE_SUB('2021-04-19', 10)  -- 2021-04-09
SELECT DATE_SUB('2021-04-19', -10) -- 2021-04-29

月份增加减少

select add_months('2023-06-18', 1);          -- 2023-07-18
select add_months('2023-06-18 13:36:36', 1); -- 2023-07-18

select add_months('2023-06-18', -1);          -- 2023-05-18
select add_months('2023-06-18 13:36:36', -1); -- 2023-05-18

• 参考资料 hive官方文档

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF