暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

华为GaussDB T 时间间隔类型

墨天轮 2019-10-12
1022

时间间隔类型

时间间隔是指两个时间点之间的差。例如雇员从入职到现在工作了多少年和月,上一次交易记录到现在间隔了多少天、小时、分、秒等。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=YMINTERVAL
    SELECT 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进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论