hive求时间差 hivesql 时间差

datediff计算时间差

命令格式：
datediff(datetime1, datetime2, datepart)
用途：
计算两个时间的差值，并转换成指定的单位，如：秒。
参数说明：
● datetime1 , datetime2: datetime类型，被减数和减数，若输入为string类型会隐式转换为datetime类型后参与运算，其它类型抛异常。
● datepart: string类型常量，修改单位，yyyy、mm、dd、hh、mi、ss中的一个，指定时间差值的单位，也支持扩展的日期格式, 年-“year”, 月-“month”或”mon”, 日-“day”, 小时-“hour”。。若datepart不符合指定的几种pattern或者其它类型则会发生异常。
返回值：
返回时间差值，int类型。任一输入参数是NULL，返回NULL。
备注：
计算时会按照datepart切掉低单位部分，然后再计算结果。
示例：
若

start = ‘2005-12-31 23:59:59’, end = ‘2006-01-01 00:00:00’:
 datediff(end, start, ‘dd’) = 1
 datediff(end, start, ‘mm’) = 1
 datediff(end, start, ‘yyyy’) = 1
 datediff(end, start, ‘hh’) = 1
 datediff(end, start, ‘mi’) = 1
 datediff(end, start, ‘ss’) = 1

创建一个测试数据

CREATE TABLE IF NOT EXISTS time_test(
    id  INT 
    ,create_time STRING 
    ,update_time STRING 

)
COMMENT 'time_test';

INSERT INTO time_test
VALUES (1,'2020-05-25','2020-05-25 12:00:00'),
(2,'2020-05-25','2020-05-26 10:00:00'),
(3,'2020-05-26','2020-05-26 12:30:00'),
(4,'2020-05-26','2020-05-26 14:00:00'),
(5,'2020-05-27','2020-05-27 08:00:00'),
(6,'2020-05-27','2020-05-27 15:00:00');

其实按照正常逻辑来计算时间差比如计算updatetime与今天的时间差天

需要如下代码

--GETDATE获取当前时间，unix_timestamp将字符串格式转化为时间戳，from_unixtime将时间戳转化为时间格式
SELECT id,DATEDIFF(GETDATE(),from_unixtime(unix_timestamp(update_time)),'dd' ) as diff FROM time_test;

但是前面文档里面我们标红的地方可以看到若输入为string类型会隐式转换为datetime类型后参与运算，也就是DATEDIFF函数其实会自动转换，所以代码其实可以直接写成

SELECT id,DATEDIFF(GETDATE(),update_time,'dd' ) as diff FROM time_test;

结果如下：

id	diff
1	2
2	1
3	1
4	1
5	0
6	0

SELECT GETDATE() AS t;–2020-05-27 17:43:00当前时间【5月27日】

同时可以根据时间差来进行条件的筛选

SELECT * FROM time_test WHERE DATEDIFF(GETDATE(),update_time,'dd' )=1;

id	create_time	update_time
2	2020-05-25	2020-05-26 10:00:00
3	2020-05-26	2020-05-26 12:30:00
4	2020-05-26	2020-05-26 14:00:00

GETDATE获取当前时间以及之前时间

• getdate
  命令格式：
  getdate()
  用途：
  获取当前系统时间
  返回值：
  返回当前日期和时间，datetime类型
• dateadd
  命令格式：
  dateadd(datetime, delta, datepart)
  用途：
  按照指定的单位和幅度修改datetime的值
  参数说明：
  ● datetime:datetime类型，日期值。若输入为string类型会隐式转换为datetime类型后参与运算，其它类型抛异常。。
  ● delta:bigint类型，修改幅度。若输入为string类型或double型会隐式转换到biging类型后参与运算，其他类型会引发异常。若delta大于0，加；否则减。
  ● datepart: string类型常量, 修改单位，，支持格式对天的修改 “dd” , 对月的修改 “mm”，对年的修改 “yyyy” ，对小时修改"hh",对分钟修改"mi"，对秒修改"ss" ，此外也支持扩展的日期格式, 年-“year”, 月-“month”或”mon”, 日-“day”, 小时-“hour”。 非常量、不支持的格式会或其它类型抛异常。
• lastday
  命令格式：
  lastday(datetime)
  用途：
  取一个月的最后一天, 截取到天，时分秒部分为00:00:00。
  参数说明：
  ● datetime: datetime格式的日期值，若输入为string类型会隐式转换为datetime类型后参与运算， 其它类型报异常。

SELECT GETDATE() AS t;--当前时间【5月27】

SELECT  dateadd(GETDATE(), -1, 'mm') as t;--当前时间减去一个月【4月27】

SELECT LASTDAY(dateadd(GETDATE(), -1, 'mm')) as t;--上个月的最后一天【4月30】

SELECT dateadd(LASTDAY(dateadd(GETDATE(), -2, 'mm')),1,'dd') as t;--上个月的第一天【4月1】