Inceptor如何计算时间差

概要描述

inceptor如何通过timestampdiff函数等方式，计算各种时间单位的时间差

这里介绍下timestampdiff函数：

参数说明：
unit：表示时间差的单位，取值：year、quarter、month、week、day、hour、minute、second。
start_timestamp：表示开始时间的日期或时间戳。
end_timestamp：表示结束时间的日期或时间戳。

注意：低版本inceptor是不支持timestampdiff函数的，需要使用其他函数改写。

详细说明

1. 毫秒

SELECT
	MILLISECOND('2019-01-01 22:10:10.111') - MILLISECOND('2019-01-01 22:10:10.100') +
	(
		unix_timestamp(cast('2019-01-01 22:10:10.111' AS TIMESTAMP)) -
		unix_timestamp(cast('2019-01-01 22:10:10.100' AS TIMESTAMP))
 	)*1000
FROM system.dual;

2. 秒

SELECT timestampdiff('second', '2019-01-01 22:00:10', '2019-01-01 22:10:10') AS result;

SELECT
	unix_timestamp('2019-01-01 22:10:10','yyyy-MM-dd HH:mm:ss') -
	unix_timestamp('2019-01-01 22:00:10','yyyy-MM-dd HH:mm:ss')
FROM system.dual;

3. 分钟

SELECT timestampdiff('minute', '2019-01-03 22:00:10', '2019-01-02 23:10:20') AS result;

SELECT
	(minute('2019-01-02 23:10:20')-minute('2019-01-03 22:00:10')) +
	(
		hour('2019-01-02 23:10:20') - hour('2019-01-03 22:00:10') +
		(
			datediff('2019-01-02 23:10:20','2019-01-03 22:00:10')
		)*24
	)*60
FROM system.dual;

SELECT
	(
	unix_timestamp('2019-01-02 23:10:20','yyyy-MM-dd HH:mm:ss') -
	unix_timestamp('2019-01-03 22:00:10','yyyy-MM-dd HH:mm:ss')
	)/60
FROM system.dual;

4. 小时

SELECT timestampdiff('hour', '2019-01-03 22:00:10', '2019-01-02 23:00:10') AS result;

SELECT
	hour('2019-01-02 23:00:10')-hour('2019-01-03 22:00:10') +
	(
		datediff('2019-01-02 23:00:10','2019-01-03 22:00:10')
	)*24
	as hour_subValue
FROM system.dual;

SELECT
	(
	unix_timestamp('2019-01-02 23:00:10','yyyy-MM-dd HH:mm:ss') -
	unix_timestamp('2019-01-03 22:00:10','yyyy-MM-dd HH:mm:ss')
	)/3600
FROM system.dual;

5. 天

SELECT timestampdiff('day', '2015-02-08 22:10:10','2015-01-15 22:10:10') AS result;

SELECT
	DATEDIFF('2015-01-15','2015-02-08')
FROM system.dual;

SELECT
	(
	unix_timestamp('2015-01-15 22:10:10','yyyy-MM-dd HH:mm:ss') -
	unix_timestamp('2015-02-08 22:10:10','yyyy-MM-dd HH:mm:ss')
	)/3600/24
FROM system.dual;

6. 星期

SELECT timestampdiff('week', '2015-01-15','2015-02-08') AS result;

SELECT
	floor(abs(DATEDIFF('2015-01-15','2015-02-08')/7))
FROM system.dual;

SELECT
	(
	unix_timestamp('2015-01-15','yyyy-MM-dd') -
	unix_timestamp('2015-02-08','yyyy-MM-dd')
	)/3600/24/7 
FROM system.dual;

7. 月

SELECT timestampdiff('month', '2010-10-15','2011-01-01') AS result;

SELECT
	MONTHS_BETWEEN('2010-10-15','2011-01-01')
FROM system.dual;

SELECT
	EXTRACT(MONTH FROM '2010-10-15') - EXTRACT(MONTH FROM '2011-01-01')+
	(
	EXTRACT(YEAR FROM '2010-10-15') - EXTRACT(YEAR FROM '2011-01-01')
	)*12
FROM system.dual;

8. 年

SELECT timestampdiff('year', '2015-01-15','2016-01-15') AS result;

SELECT
	EXTRACT(YEAR FROM '2015-01-15') - EXTRACT(YEAR FROM '2016-01-15') 
FROM system.dual;

--注意不要用大写的YYYY，在java下YYYY是week-based-year
SELECT
	to_char('2026-12-31', 'yyyy') - to_char('2016-12-31', 'yyyy')
FROM system.dual;