使用hive时，常用的时间函数，数据仓库必须掌握这些时间函数

Spark大数据 2021-08-14

2059

在使用hive建设数据仓库的过程或者做数据模型、数据分析时，经常会用到各种各样的时间日期。诸如，求7天前、15天前的数据，查询昨天或当天几个小时内的数据，这些都需要用到时间函数，要求我们拿到具体的时间才能进行下一步工作。

一、Hive中常用的几种日期处理函数

1，datediff函数，datediff函数用来求两个指定日期之间的差，用于返回两个天数之间的日期。

语法：datediff(date1,date2)

说明：date1 和 date2 要是参数是合法的日期或日期/时间表达式。

当日期 date1<date2 时函数返回值为正数，当 date1=date2 时函数返回值为0，当 date1>date2 时函数返回值为负数。

//查询过去n天日期 包含当天 小于等于0
SELECT * FROM dual WHERE DATEDIFF(日期字段,NOW()) <=0 AND DATEDIFF(日期字段,NOW())>-n


//查询过去n天日期 不包含当天 小于 0
SELECT * FROM dual WHERE DATEDIFF(日期字段,NOW()) <0 AND DATEDIFF(日期字段,NOW())>-n

例子：

表里原始数据：
date_code
2021-08-09
2021-08-10
2021-08-11
2021-08-12
2021-08-13
2021-08-14
2021-08-15
2021-08-16
2021-08-17
2021-08-18
2021-08-19
2021-08-20




//查询过去10天内的数据，且不包含当天
SELECT 
    date_code
FROM dual 
WHERE 
    datediff(date_code,current_date()) < 0 
AND 
    datediff(date_code,current_date()) > -10


返回结果：
2021-08-11
2021-08-12
2021-08-13
2021-08-14
2021-08-15
2021-08-16
2021-08-17
2021-08-18
2021-08-19




//查询过去10天内的数据，含当天
SELECT 
    date_code
FROM dual 
WHERE 
    datediff(date_code,current_date()) <= 0 
AND 
    datediff(date_code,current_date()) > -10


返回结果：
2021-08-11
2021-08-12
2021-08-13
2021-08-14
2021-08-15
2021-08-16
2021-08-17
2021-08-18
2021-08-19
2021-08-20

注意now() 、current_timestamp()、current_date()这三种获取当前系统时间的区别：now() 、current_timestamp()、current_date()三者都是获取当前系统时间，但now() 、current_timestamp()获取的是带时分秒的时间，current_date()获取的是不带时分秒的时间。

SELECT
  now() AS date1,
  current_date() AS date2,
  current_timestamp() AS date3
FROM dual


返回结果：
2021-08-14 15:49:13.53  
2021-08-14  
2021-08-14 15:49:13.53

2，date_add 日期增加函数，查询并返回未来天数

语法：date_add(startdate, days)

说明：返回开始日期startdate增加days天后的日期。

//查询未来n天日期
SELECT DATE_ADD(日期字段,n)  FROM dual

例子：

//查询10天后的日期
SELECT 
     date_add('2021-08-14',10) AS date_code
FROM 
    dual 
 
 返回结果：
 2021-08-24

3，date_sub 日期减少函数，查询并返回过去的天数

语法：date_sub(startdate, days)

说明：返回开始日期startdate减少days天后的日期。

//查询过去n天的日期
SELECT DATE_SUB(日期字段,n)  FROM dual

例子：

//查询10天前的日期
SELECT 
     date_add('2021-08-14',10) AS date_code
FROM 
    dual 
 
 返回结果：
 2020-08-04

上述三种日期函数的应用

1，查询当天的所有数据

SELECT * FROM tablename WHERE DATEDIFF(日期字段,NOW()) = 0

2，查询昨天的所有数据

SELECT * FROM tablename WHERE DATEDIFF(日期字段,NOW())=-1

3，查询第n天的所有数据。注意当n为负数时，表示查询过去第n天的数据；当n为正数时，表示查询未来第n天的数据

//当n为负数时，表示过去第n天的数据
 SELECT * FROM tablename DATEDIFF(字段,NOW())=n

二、日期与时间戳的使用

timestamp
timestamp是UTC时间，可以是以秒为单位的整数，带精度的浮点数，最大精确到小数点后9位，纳秒级；格式化后的字符串 yyyy-MM-dd HH:mm:ss.fffffffff

Date
Hive中的Date只支持yyyy-MM-dd格式的日期，其余写法都是错误的，如需带上时分秒，请使用timestamp。

例子：

--建带有timestamp格式字段的表
rcreate table time_dual(time timestamp);


--插入一个数据（一个数据占一个文件）
insert into table time_dual values('2021-08-14 15:30:49.223');


--建带有date格式字段的表
create table date_dual(dt date);


--插入一个数据，虽然命令没报错，但却没有将数据写入文件。
insert into table date_dual values('2021-08-14 15:18:48.807');


--将日期覆盖插入的date_dual（将目录下的所有都删掉，再写入）
insert overwrite table date_dual values('2021-08-14');

注意：into是插入，overwrite是覆盖。

获取当前timestamp：

 select current_timestamp() from dual --返回值: timestamp时间戳

获取当前日期：

select current_date() from dual --返回值：date日期时间

格式化timestamp/date为字符串：

 date_format(date/timestamp/string ts, string fmt)--返回值：字符串日期

Unix时间戳

把当前时间或时间字符串转为 Unix时间戳

 --获取当前timestamp的Unix时间戳
select unix_timestamp(current_timestamp);


--获取指定字符串的Unix时间戳
select unix_timestamp('2021-08-14 12:00:00');

把Unix时间戳转为时间字符串

 from_unixtime(bigint unixtime[string format])
 --unixtime：从1970-01-01 00:00:00 UTC到指定时间的秒数
 --format：目标转换格式
 --返回值: stringb

例子：

 --转换成 yyyy-MM-dd HH:mm:ss 类型的时间
 select from_unixtime(1530755469) from dual;


--转换成指定格式的字符串
select from_unixtime(1530755469, "yyyy-MM-dd") from dual;

数据库

文章转载自Spark大数据，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

使用hive时，常用的时间函数，数据仓库必须掌握这些时间函数

一、Hive中常用的几种日期处理函数

上述三种日期函数的应用

二、日期与时间戳的使用

评论