概要描述

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;