求两个时间段的日期差:
在db2中求两个时间段日期的差:以20180329 - 20180123为例
days(date(2018||'-'||03||'-'||29))-days(date(substr(20180123,1,4)||'-'||substr(20180123,5,2)||'-'||substr(20180123,7,2)))
先将字符串转换成date类型、然后通过days函数进行计算,在db2中字符串的拼接用||
days(date(2018||'-'||03||'-'||29)) 被减数
days(date(substr(20180123,1,4)||'-'||substr(20180123,5,2)||'-'||substr(20180123,7,2))) 减数
在hive中的写法:
datediff(concat(2018,'-',03,'-',29),concat(substr(20180123,1,4),'-',substr(20180123,5,2),'-',substr(20180123,7,2)))
hive中字符串拼接用concat函数计算两个时间段天数差用户datediff
concat(2018,'-',03,'-',29) 被减数
concat(substr(20180123,1,4),'-',substr(20180123,5,2),'-',substr(20180123,7,2)) 减数
求两个时间段的月份差:
db2向下取整:
timestampdiff(64,CHAR(TIMESTAMP('2018-03-29')-TIMESTAMP('2017-03-01')))
实际为12月28天结果为12
在hive中的写法:
floor(months_between('2018-03-29','2017-03-01'))
结果也为12
其中floor在hive中为向下取整,round四舍五入,ceil向上取整