时间间隔类型
时间间隔是指两个时间点之间的差。例如雇员从入职到现在工作了多少年和月，上一次交易记录到现在间隔了多少天、小时、分、秒等。GaussDB 100支持两种时间间隔数据类型：INTERVAL YEAR TO MONTH和INTERVAL DAY TO SECOND，可以分别简写为YMINTERVAL、DSINTERVAL。
INTERVAL YEAR TO MONTH
语法：
INTERVAL YEAR[(n)] TO MONTH功能：存储一个若干年若干月的时间间隔。
取值范围：[-9999-11, +9999-11]
- n的取值范围是[0,4]，表示年的精度，默认值为2。
- INTERVAL YEAR TO MONTH的最小取值为-9999-11，表示时间差为负9999年11月; 同理，最大取值+9999-11表示时间差9999年11月。
占用空间：4字节。
示例：
指定一个INTERVAL YEAR TO MONTH列的定义：ymitvl_col_name INTERVAL YEAR [(year_precision)] TO MONTH
- year_precision指示了YEAR域的精度，即表示YEAR的最大有效数字个数。
- 若设置year_precision为2，则表示YEAR域的最大值为99，若用户指定的值超出这个范围将会报错。
- year_precision最小可以设置为0，最大可以设置为4，默认值为2。若用户输入超出指定精度则报错。
INTERVAL DAY TO SECOND
语法：
INTERVAL DAY［(n1)］ TO SECOND [(n2)]功能：存储一个若干天(包括天、时、分、秒、微秒)的时间间隔。
取值范围：[-9999999 23:59:59.999999,+9999999 23:59:59.999999]
- n1取值范围是[0,7]，表示天的精度，默认值为2。
- n2取值范围是[0,6]，表示秒后面的精度，不指定时默认为6。
- INTERVAL DAY TO SECOND的最小取值为-9999999 23:59:59.999999，表示时间差负999999天23小时59分钟59.999999秒。
占用空间：8字节。
示例：
指定一个INTERVAL DAY TO SECOND列的定义：dsitvl_col_name INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]
- day_precision表示DAY域的精度，效果同year_precision。
- 其最小值可以设置为0，最大可以设置为7，默认值为2。
- fractional_seconds_precision表示指定秒SECOND域小数点后面的精度，其最小值可以设置为0，最大可以设置为6，默认值为6。
- SECOND域的小数部分输入允许超出指定精度，但在输入后会按照指定精度用四舍五入规则进行截取，使得结果满足指定精度要求。
例如，指定fractional_seconds_precision为6，对于输入'1 12:12:12.666666666666'将会被截取为'1 12:12:12.666667'。
INTERVAL类型的比较
- INTERVAL类型可以自身类型进行比较，INTERVAL YEAR TO MONTH和INTERVAL DAY TO SECOND之间不能比较。
- INTERVAL类型可以用字符串和文本进行比较，比较前需要将字符串按照其对应的类型进行转换，若转换失败则报错。
- INTERVAL类型不能同其他数据类型进行比较。
示例
CREATE TABLE PFA_dsitvl(id int, dsval interval day(7) to second); INSERT INTO PFA_dsitvl VALUES(1, '1231 12:3:4.1234');insert into PFA_dsitvl values(2, 'P1231DT16H3.3333333S'); INSERT INTO PFA_dsitvl VALUES(3, 'PT12H'); INSERT INTO PFA_dsitvl VALUES(4, '-P99DT655M999.99999S'); INSERT INTO PFA_dsitvl VALUES(5, '-0 00:19:7.7777777777'); INSERT INTO PFA_dsitvl VALUES(6, '-1234 0:0:0.0004');对dsval列进行ORDER BY的执行结果为：
SELECT * FROM PFA_dsitvl ORDER BY dsval; ID DSVAL -------------------------------------- 6 -0001234 00:00:00.000400 4 -0000099 11:11:39.999990 5 -0000000 00:19:07.777778 3 +0000000 12:00:00.000000 1 +0001231 12:03:04.123400 2 +0001231 16:00:03.333333 6 rows fetched.对dsval进行等值比较，如下SQL语句，其中比较的右操作数为字符串，在比较前，系统会将该字符串转换为INTERVAL DAY TO SECOND类型：
SELECT * FROM PFA_dsitvl WHERE dsval = '0000 12:0000:0.000000'; ID DSVAL -------------------------------------- 3 +0000000 12:00:00.000000 1 rows fetched.对dsval进行大小比较，例如：SELECT * FROM PFA_dsitvl WHERE dsval > 'P0000DT00000012H0000M0.0000S'; ID DSVAL -------------------------------------- 1 +0001231 12:03:04.123400 2 +0001231 16:00:03.333333 2 rows fetched.对dsval求最大值和最小值：SELECT MIN(DSVAL), MAX(DSVAL) FROM PFA_dsitvl; MIN(DSVAL) MAX(DSVAL) ---------------------------------------------------- -0001234 00:00:00.000400 +0001231 16:00:03.333333 1 rows fetched.INTERVAL类型的表达式
- INTERVAL类型之间的运算
INTERVAL类型之间可以进行加法与减法运算，但DSINTERVAL和YMINTERVAL不能进行运算。
DSINTERVAL+DSINTERVAL=DSINTERVAL
YMINTERVAL+YMINTERVAL=YMINTERVAL
DSINTERVAL-DSINTERVAL=DSINTERVAL
YMINTERVAL-YMINTERVAL=YMINTERVALSELECT numtodsinterval(22, 'HOUR') + numtodsinterval(9999999, 'DAY') FROM SYS_DUMMY; NUMTODSINTERVAL(22, 'HOUR') + ------------------------------ +9999999 22:00:00.000000 1 rows fetched. SELECT numtoyminterval(24, 'month') - numtoyminterval(9, 'month') FROM SYS_DUMMY; NUMTOYMINTERVAL(24, 'MONTH') - NUMTOYMINTERVAL(9, 'MONTH') ---------------------------------------------------------- +0001-03 1 rows fetched. SELECT numtoyminterval(24, 'month') + numtoyminterval(9998, 'year') FROM SYS_DUMMY; GS-00659, INTERVAL YEAR TO MONTH out of range
- INTERVAL类型与数值类型之间的运算
INTERVAL类型与数值类型可以进行乘法运算，表示该INTERVAL类型的多少倍，运算结果还是为对应的INTERVAL类型。
DSINTERVAL*NUMERIC=DSINTERVAL
YMINTERVAL*NUMERIC=YMINTERVAL
NUMERIC*DSINTERVAL=DSINTERVAL
NUMERIC*YMINTERVAL=YMINTERVAL
DSINTERVAL/NUMERIC=DSINTERVAL
YMINTERVAL/NUMERIC=YMINTERVAL
SELECT NUMTODSINTERVAL(1, 'day') / 2::integer FROM SYS_DUMMY; NUMTODSINTERVAL(1, 'DAY') / 2::INTEGER ---------------------------------------- +0000000 12:00:00.000000 1 rows fetched. SELECT NUMTODSINTERVAL(1, 'day') / 1.33::real FROM SYS_DUMMY; NUMTODSINTERVAL(1, 'DAY') / 1.33::REAL -------------------------------------- +0000000 18:02:42.406015 1 rows fetched. SELECT NUMTOYMINTERVAL(1, 'year') / 2::integer FROM SYS_DUMMY; NUMTOYMINTERVAL(1, 'YEAR') / 2::INTEGER ---------------------------------------- +0000-06 1 rows fetched. SELECT NUMTOYMINTERVAL(1, 'year') / 5::bigint FROM SYS_DUMMY; NUMTOYMINTERVAL(1, 'YEAR') / 5::BIGINT --------------------------------------- +0000-02 1 rows fetched. SELECT NUMTODSINTERVAL(1, 'day') * 2::integer FROM SYS_DUMMY; NUMTODSINTERVAL(1, 'DAY') * 2::INTEGER -------------------------------------- +0000002 00:00:00.000000 1 rows fetched. SELECT NUMTODSINTERVAL(1, 'day') * 1.33::number FROM SYS_DUMMY; NUMTODSINTERVAL(1, 'DAY') * 1.33::NUMBER ----------------------------------------- +0000001 07:55:12.000000 1 rows fetched. SELECT 0.3 * NUMTODSINTERVAL(1, 'day') FROM SYS_DUMMY; 0.3 * NUMTODSINTERVAL(1, 'DAY') ------------------------------- +0000000 07:12:00.000000 1 rows fetched.
- DSINTERVAL类型与时间类型之间的运算
DSINTERVAL类型与时间类型之间可以进行加减运算，表示在当前时间基础上加上或减去一个DSINTERVAL值。这里的DATETIME可以为DATE、TIMESTAMP、TIMESTAMP_TZ和TIMESTAMP_LTZ类型。
DSINTERVAL+DATETIME=DATETIME
DATETIME+DSINTERVAL=DATETIME
DATETIME-DSINTERVAL=DATETIME
SELECT NUMTODSINTERVAL(1, 'day') + TO_DATE('2016-02-29', 'YYYY-MM-DD') FROM SYS_DUMMY; NUMTODSINTERVAL(1, 'DAY') + TO_DATE('2016-02-29', 'YYYY-MM-DD') --------------------------------------------------------------- 2016-03-01 00:00:00 1 rows fetched. SELECT TO_DATE('2018-08-02', 'YYYY-MM-DD') + NUMTODSINTERVAL(10, 'day') FROM SYS_DUMMY; TO_DATE('2018-08-02', 'YYYY-MM-DD') + NUMTODSINTERVAL(10, 'DAY') ---------------------------------------------------------------- 2018-08-12 00:00:00 1 rows fetched. SELECT TO_DATE('2019-06-29', 'YYYY-MM-DD') - NUMTODSINTERVAL(10, 'HOUR') FROM SYS_DUMMY; TO_DATE('2019-06-29', 'YYYY-MM-DD') - NUMTODSINTERVAL(10, 'HOUR') ---------------------------------------------------------------- 2019-06-28 14:00:00 1 rows fetched.
- YMINTERVAL类型与时间类型之间的运算
INTERVAL YEAR TO MONTH和时间类型也可以进行加减运算。计算规则是在时间类型的YEAR和MONTH域上进行。
YMINTERVAL+DATETIME=DATETIME
DATETIME+YMINTERVAL=DATETIME
DATETIME-YMINTERVAL=DATETIME
需要注意的是，若相加后结果DAY域的值和YEAR和MONTH不兼容则报错。例如以下SQL语句将会报错，因为相加后的日期为2018-09-31，但九月最多30天。
SELECT TO_DATE('2018-08-31', 'YYYY-MM-DD') + NUMTOYMINTERVAL(1, 'month') FROM SYS_DUMMY; GS-00674, DATETIME with illegal month field相似的情况还可能发生在闰年的年份，这里2016年是闰年，2月份有29天，当加上一年后，结果为2017年，但2017年不是闰年，其二月份最多28天，因此也会产生错误。
SELECT TO_DATE('2016-02-29', 'YYYY-MM-DD') + TO_YMINTERVAL('P1Y') FROM SYS_DUMMY; GS-00674, DATETIME with illegal month field其他例子
SELECT TO_DATE('2018-01-31', 'YYYY-MM-DD') + NUMTOYMINTERVAL(1, 'year') FROM SYS_DUMMY; TO_DATE('2018-01-31', 'YYYY-MM-DD') + NUMTOYMINTERVAL(1, 'YEAR') ------------------------------------------------------------------ 2019-01-31 00:00:00 1 rows fetched.- 时间类型的减法运算
支持时间类型的减法运算，其中DATE与DATE的减法结果为数值类型，DATE和TIMESTAMP的减法结果类型为DSINTERVAL类型。
DATETIME-TIMESTAMP=DSINTERVAL
TIMESTAMP-DATETIME=DSINTERVAL
DATE-DATE=DECIMAL
SELECT TO_CHAR(TO_DATE('9999-05-23 11', 'YYYY-MM-DD HH24') - TO_DATE('2018-01-01 00', 'YYYY-MM-DD HH24')) FROM SYS_DUMMY; TO_CHAR(TO_DATE('9999-05-23 11 ---------------------------------------------------- 2915142.45833333333333333333333333333333 1 rows fetched. SELECT TO_DATE('9999-05-23 11', 'YYYY-MM-DD HH24') - TO_TIMESTAMP('2018-01-01 00', 'YYYY-MM-DD HH24') FROM SYS_DUMMY; TO_DATE('9999-05-23 11', 'YYYY-MM-DD HH24') - TO_TIMESTAMP('2018 ---------------------------------------------------------------- +2915142 11:00:00.000000 1 rows fetched. SELECT * FROM SYS_DUMMYWHERE SYSTIMESTAMP - SYSTIMESTAMP < '0 0:0:30'; DUMMY ----- X 1 rows fetched. SELECT TO_TIMESTAMP('9999-05-23 11', 'YYYY-MM-DD HH24') - TO_TIMESTAMP('2018-01-01 00', 'YYYY-MM-DD HH24') FROM SYS_DUMMY; TO_TIMESTAMP('9999-05-23 11', ------------------------------ +2915142 11:00:00.000000 1 rows fetched.「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」关注作者【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。评论
