INTERVAL

INTERVAL 数据类型

Lakehouse 提供了 INTERVAL 数据类型，用来表示两个日期或时间之间的时间间隔。本文为您介绍 INTERVAL 类型的使用方法和语法。

使用说明

INTERVAL 数据类型支持两种类型的间隔：

  • INTERVAL_YEAR_MONTH：表示年月间隔，使用 YEAR 和 MONTH 字段存储时间间隔。
  • INTERVAL_DAY_TIME：表示日间间隔，使用包括小数秒在内的天、小时、分钟和秒存储间隔。

INTERVAL_YEAR_MONTH

语法格式

语法描述示例
INTERVAL '[+|-]y-m' YEAR TO MONTH同时指定 YEAR 和 MONTH 间隔INTERVAL '2-3' YEAR TO MONTH 表示 2 年 3 个月
INTERVAL '[+|-]n' YEAR仅指定 YEAR 间隔INTERVAL '2' YEAR 表示 2 年
INTERVAL '[+|-]n' MONTH仅指定 MONTH 间隔INTERVAL '3' MONTH 表示 3 个月
INTERVAL '[+|-]n' QUARTER支持季度作为独立单位INTERVAL '4' QUARTER 表示 4 个季度（即 1 年）

参数说明

  • year：取值范围为 [0, 178956970]。
  • month：取值范围为 [0, 11]（在 YEAR TO MONTH 格式中）。
  • quarter：季度单位标识。1 QUARTER = 3 MONTH。支持任意正整数或负整数，自动转换为 MONTH 进行计算。

注意事项

  • 仅指定 MONTH 间隔时，month 取值可以超过 11，超过部分会折算为 YEAR。例如 INTERVAL '25' MONTH 等同于 2 年 1 个月。
  • INTERVAL_YEAR_MONTH 的最大值为 INTERVAL '178956970-7' YEAR TO MONTH（约 1.79 亿年）。

INTERVAL_DAY_TIME

语法格式

语法描述示例
INTERVAL '[+|-]n' DAY仅指定 DAY 间隔INTERVAL '1' DAY 表示 1 天
INTERVAL '[+|-]n' HOUR仅指定 HOUR 间隔INTERVAL '23' HOUR 表示 23 小时
INTERVAL '[+|-]n' MINUTE仅指定 MINUTE 间隔INTERVAL '59' MINUTE 表示 59 分钟
INTERVAL '[+|-]n' SECOND仅指定 SECOND 间隔INTERVAL '59.999' SECOND 表示 59.999 秒
INTERVAL '[+|-]d h' DAY TO HOUR指定 DAY 和 HOURINTERVAL '1 23' DAY TO HOUR 表示 1 天 23 小时
INTERVAL '[+|-]d h:m' DAY TO MINUTE指定 DAY、HOUR、MINUTEINTERVAL '1 23:59' DAY TO MINUTE 表示 1 天 23 小时 59 分钟
INTERVAL '[+|-]d h:m:s' DAY TO SECOND指定 DAY、HOUR、MINUTE、SECONDINTERVAL '1 23:59:59.999' DAY TO SECOND 表示 1 天 23 小时 59 分 59.999 秒
INTERVAL '[+|-]h:m' HOUR TO MINUTE指定 HOUR 和 MINUTEINTERVAL '15:40' HOUR TO MINUTE 表示 15 小时 40 分钟
INTERVAL '[+|-]h:m:s' HOUR TO SECOND指定 HOUR、MINUTE、SECONDINTERVAL '15:40:32.999' HOUR TO SECOND 表示 15 小时 40 分 32.999 秒
INTERVAL '[+|-]m:s' MINUTE TO SECOND指定 MINUTE 和 SECONDINTERVAL '40:32.999' MINUTE TO SECOND 表示 40 分 32.999 秒

参数说明

  • day：取值范围为 [0, 106751991]。
  • hour：取值范围为 [0, 23]（在复合格式中）。
  • minute：取值范围为 [0, 59]（在复合格式中）。
  • second：取值范围为 [0, 59.999999999]（在复合格式中）。

注意事项

  • 仅指定 HOUR/MINUTE/SECOND 间隔时，对应参数的取值可以超过范围上限，超过的部分会被折算为更大的单位。
  • INTERVAL_DAY_TIME 的最大值为 INTERVAL '106751991 04:00:54.775807' DAY TO SECOND。

多单位混合写法

支持在一个 INTERVAL 表达式中同时指定多个单位，系统会自动将各单位的值合并计算：

SELECT interval 1 year 2 month 3 week 4 day 5 hour 6 minute 7 seconds 8 millisecond 9 microsecond;

支持的单位包括：YEAR、MONTH、WEEK、DAY、HOUR、MINUTE、SECOND（SECONDS）、MILLISECOND、MICROSECOND、NANOSECOND。

其中 WEEK 会自动转换为 7 天，MILLISECOND/MICROSECOND/NANOSECOND 会折算为秒的小数部分。

也可以使用字符串形式分段指定各单位的值：

SELECT interval '30' year '25' month '-100' day '40' hour '80' minute '299.889987299' second;

表达式写法

允许 INTERVAL expr unit 形式，其中 expr 可以是任意表达式（不限于字面量），支持多单位混合：

-- 简单算术表达式
SELECT interval 1+2 year AS res;
-- 结果：3-0

-- 括号表达式
SELECT interval (-30) day;
-- 结果：-30 00:00:00.000000000

-- 函数调用作为值
SELECT interval -(1) days abs(-2) hours;
-- 结果：-0 22:00:00.000000000

-- 多单位混合表达式
SELECT interval 3 hours 2+3 minutes sin(1) seconds;
-- 结果：0 03:05:00.841470000

-- 引用列值
SELECT interval i second i millisecond i microseconds FROM VALUES(1) t(i);
-- 结果：0 00:00:01.001001000

-- 年月类型的表达式
SELECT interval 1+1 year 2+2 month;
-- 结果：2-4

宽松写法

INTERVAL 支持多种灵活的书写方式：

-- 直接使用 WEEK 单位
SELECT interval 7 week;
-- 结果：49 00:00:00.000000000

-- 数字写在字符串中
SELECT interval '7' week;
-- 结果：49 00:00:00.000000000

-- 数字和单位都写在字符串中
SELECT interval '7 week';
-- 结果：49 00:00:00.000000000

INTERVAL 类型的运算

INTERVAL 类型支持与数值类型、日期时间类型、字符串类型进行算术运算。

基本算术运算

-- 乘法和除法
SELECT INTERVAL 4 DAY * 2, INTERVAL 4 DAY / 2;
-- 结果：8 00:00:00.000000000    2 00:00:00.000000000

-- 时间戳相减得到 INTERVAL
SELECT timestamp '2019-10-15' - timestamp '2019-10-14';
-- 结果：1 00:00:00.000000000

-- 日期/时间戳加减 INTERVAL
SELECT timestamp '2020-10-10' + INTERVAL 1 DAY, date '2020-10-10' + INTERVAL 1 MONTH;
-- 结果：2020-10-11 00:00:00     2020-11-10

INTERVAL 之间的运算

-- 同类型 INTERVAL 加减
SELECT interval '2-2' year to month + interval '3' month;
-- 结果：2-5

SELECT interval '99 11:22:33.123456789' day to second + interval '10 9:8' day to minute;
-- 结果：109 20:30:33.123456789

比较运算

SELECT INTERVAL 1 DAY < INTERVAL 2 DAY;
-- 结果：true

SELECT INTERVAL '1' YEAR < INTERVAL '1' MONTH;
-- 结果：false

SELECT INTERVAL '-1-1' YEAR TO MONTH = INTERVAL '-13' MONTH;
-- 结果：true

数学函数

-- 绝对值
SELECT abs(INTERVAL '-10' YEAR);
-- 结果：10-0

SELECT abs(INTERVAL -'1 02:03:04.123' DAY TO SECOND);
-- 结果：1 02:03:04.123000000

-- 符号函数
SELECT signum(INTERVAL '-10' DAY);
-- 结果：-1.0

SELECT signum(INTERVAL '0-0' YEAR TO MONTH);
-- 结果：0.0

与 NULL 的运算

SELECT interval '2' year + null;
-- 结果：NULL

SELECT null + interval '2' hour;
-- 结果：NULL

字符串与 INTERVAL 的运算

支持将符合 INTERVAL 格式的字符串与 INTERVAL 进行加减运算：

SELECT '4 12:12:12' + interval '4 22:12' day to minute;

构造函数

make_ym_interval

构造 INTERVAL_YEAR_MONTH 类型的值：

SELECT make_ym_interval(1);        -- 1 年
-- 结果：1-0

SELECT make_ym_interval(1, 2);     -- 1 年 2 个月
-- 结果：1-2

make_dt_interval

构造 INTERVAL_DAY_TIME 类型的值：

SELECT make_dt_interval(1);              -- 1 天
-- 结果：1 00:00:00.000000000

SELECT make_dt_interval(1, 2);           -- 1 天 2 小时
-- 结果：1 02:00:00.000000000

SELECT make_dt_interval(1, 2, 3);        -- 1 天 2 小时 3 分钟
-- 结果：1 02:03:00.000000000

SELECT make_dt_interval(1, 2, 3, 4.005006);  -- 1 天 2 小时 3 分钟 4.005006 秒
-- 结果：1 02:03:04.005006000

注意事项

  • INTERVAL_YEAR_MONTH 和 INTERVAL_DAY_TIME 是两种不同的类型，不能直接相加减。例如 interval '2-2' year to month + interval '3' day 会报错。
  • 不同类型的 INTERVAL 不能进行比较运算。例如 INTERVAL 1 MONTH > INTERVAL 20 DAYS 会报错。
  • INTERVAL 类型不支持存储到 Iceberg 格式的表中。
TIMESTAMP_NTZ
BINARY
Yunqi © 2024 Yunqi, Inc. All Rights Reserved.

联系我们
预约咨询
微信咨询
电话咨询