间隔函数
NUMTODSINTERVAL
语法:
NUMTODSINTERVAL(num, 'interval_unit')
功能:输入一个数值和interval域描述字段,输出INTERVAL DAY TO SECOND类型。
其中参数num可以是:
- 一个数值类型: 整数、大整数、浮点数、高精度数值类型NUMBER 。
- 可以隐式转换为数值的表达式。
具体的输入输出关系,见表1。
表1 NUMTODSINTERVAL输入输出关系表 示例输入
输出结果
说明
numtodsinterval(3.1425926535897932384626, 'DAY')
+0000003 03:25:20.005270
-
numtodsinterval(3.1425926535897932384626, 'MINUTE')
+0000000 00:03:08.555559
-
numtodsinterval(999999999.99999, 'second')
+0011574 01:46:39.999990
-
numtodsinterval(199.99999, 'year')
错误
域指示符错误
说明:参数interval_unit是一个字符串类型,指示了num对应INTERVAL类型的域。对于NUMTODSINTERVAL函数,其interval_unit可以为:DAY、HOUR、MINUTE和SECOND。注意interval_unit对大小写不敏感,首尾空格会被忽视。
示例:
返回当前时间1小时后的时间。
SELECT SYSDATE + NUMTODSINTERVAL(1, 'HOUR') from SYS_DUMMY; SYSDATE + NUMTODSINTERVAL(1, 'HOUR') ------------------------------------ 2018-12-05 10:42:39 1 rows fetched.
NUMTOYMINTERVAL
语法:
NUMTOYMINTERVAL(num, 'interval_unit')
功能:输入一个数值和interval域描述字段,输出INTERVAL YEAR TO MONTH类型。
说明:
- 参数interval_unit是一个字符串类型,指示了num对应INTERVAL类型的域。对于NUMTOYMINTERVAL函数,其interval_unit可以为:YEAR和MONTH。注意interval_unit对大小写不敏感,首尾空格会被忽视。
- 其中参数num可以是:
- 一个数值类型: 整数、大整数、浮点数、高精度数值类型NUMBER 。
- 可以隐式转换为数值的表达式。
具体的输入输出关系,见表2。
表2 NUMTOYMINTERVAL输入输出关系 示例输入
输出结果
说明
numtoyminterval(9999.9, 'year')
+9999-11
-
numtoyminterval(9999.99, 'year')
错误
四舍五入导致越界
numtoyminterval(9999.9999999999999, 'year')
错误
四舍五入导致越界
numtoyminterval(99999.9, 'month')
+8333-04
-
numtoyminterval('123', 'month')
+0010-03
该字符串可以转换为数值类型
numtoyminterval('123XX', 'month')
错误
字符串转数值类型失败
numtoyminterval(+3.1425926535897932384626, 'year')
+0003-02
-
示例:
返回当前时间1个月后的时间。
SELECT SYSDATE + NUMTOYMINTERVAL(1, 'MONTH') from SYS_DUMMY; SYSDATE + NUMTOYMINTERVAL(1, 'MONTH') ------------------------------------- 2019-01-05 09:33:28 1 rows fetched.
TO_DSINTERVAL
语法:
TO_DSINTERVAL(str_exp)
功能:输入INTERVAL字符串,输出INTERVAL DAY TO SECOND的值。
用于表示间隔中的天(DAY)和时间(包括时、分、秒、微秒)。适用于表示更精确的时间。
TO_DSINTERVAL支持两种文本输入格式:
- SQL时间间隔格式: 与SQL标准(ISO/IEC 9075:2003)兼容。
- ISO时间间隔格式: 与ISO 8601:2004标准兼容。
说明:
- TO_DSINTERVAL的ISO格式是至少要从DAY开始,不能指定YEAR或MONTH单元,否则会报语法错误。
- 在SQL间隔格式中,每个格式元素之间允许空格存在;但在ISO间隔格式中,格式元素之间不允许存在空格。
- TO_DSINTERVAL的ISO格式也需要遵循TO_YMINTERVAL函数的ISO格式限制。
每个域的取值,如表3所示。
表3 取值范围表 域名称
SQL格式取值范围
ISO格式取值范围
years
[0, 9999]
[0, 9999]
months
[0, 11]
[0, 99999]
days
[0, 9999999]
[0, 9999999]
hours
[0, 23]
[0, 999999999]
minutes
[0, 59]
[0, 999999999]
seconds
[0, 59]
[0, 999999999]
frac_secs
>=0,最多存6位有效数字
>=0,最多存6位有效数字
各格式元素输出结果,如表4所示。
表4 输出结果示例表 输入
输出
说明
'-PT23H23M23S'
-00 23:23:23.000000
-
'-PT23H23M23.003333999S'
-00 23:23:23.003334
四舍五入,最多保留6位小数
'PT999999M'
+694 10:39:00.000000
-
'-P012M28DT21H213.00S'
错误
DSINTERVAL类型必须从DAY开始
'PT123123M123H'
错误
MINUTE域不能出现在HOUR后面
'-P100DT'
错误
若T被指定时,后面至少有一个时间域
'-P12DT.00123S'
错误
未指定SECOND域的值
'PT12.3H312.999999999999S'
错误
HOUR域只能为整数
'P12H'
错误
时间域必须在T之后
'PT12H12H'
错误
不允许对同一域多少赋值
'PT12H12.S'
错误
若SECOND存在小数点,则后面FRAC_SEC也必须被指定
'12 12:12:12'
+12 12:12:12.000000
-
'-0 0:0:0'
+00 00:00:00.000000
-
'9999999 23:59:59.999999'
+9999999 23:59:59.999999
-
' - 00012 0012 : 00012 : 000000000000012 '
-12 12:12:12.000000
允许首尾,域之间存在空格
'+00 0:0:0. 0'
错误
SECOND和FRAC_SEC域之间不允许存在空格
'+00 24:0:0.0'
错误
SQL格式中HOUR域最大值为23
'+00 11:80:0.0'
错误
SQL格式中MINUTE域最大值为59
'+00 11:11'
错误
格式错误
示例:
返回至2018年12月31日已经为公司工作180天的员工编号及姓名。--删除表employee。 DROP TABLE IF EXISTS employee;
--创建表employee。 CREATE TABLE employee(employee_id INT NOT NULL,first_name VARCHAR(10),last_name VARCHAR(10), hire_date DATETIME);
--插入几条数据。 INSERT INTO employee(employee_id,first_name,last_name,hire_date) VALUES(1001,'Alice','BROWN','2017-06-20 12:00:00'); INSERT INTO employee(employee_id,first_name,last_name,hire_date) VALUES(1002,'BOB','Smith','2017-10-20 12:00:00'); INSERT INTO employee(employee_id,first_name,last_name,hire_date) VALUES(1003,'ALAN','Jones','2017-05-10 12:00:00'); --提交事务。 COMMIT;
--至2018年12月31日已经为公司工作180天的员工。 SELECT * FROM employee WHERE hire_date + TO_DSINTERVAL('180 00:00:00') <= DATE '2018-12-31' ORDER BY employee_id; EMPLOYEE_ID FIRST_NAME LAST_NAME HIRE_DATE ------------ ---------- ---------- ---------------------- 1001 Alice BROWN 2017-06-20 12:00:00 1002 BOB Smith 2017-10-20 12:00:00 1003 ALAN Jones 2017-05-10 12:00:00 3 rows fetched.
TO_YMINTERVAL
语法:
TO_YMINTERVAL(str_exp)
功能:输入INTERVAL字符串,输出INTERVAL YEAR TO MONTH的值。
用于表示间隔中的年(YEAR)和月(MONTH)。适用于只关注时间差的年和月。
TO_YMINTERVAL支持两种文本输入格式:
- SQL时间间隔格式: 与SQL标准(ISO/IEC 9075:2003)兼容。
- ISO时间间隔格式: 与ISO 8601:2004标准兼容。
两种标准下不同域的取值范围,请参见表5。
表5 取值范围表 域名称
SQL格式取值范围
ISO格式取值范围
years
[0, 9999]
[0, 9999]
months
[0, 11]
[0, 99999]
days
[0, 9999999]
[0, 9999999]
hours
[0, 23]
[0, 999999999]
minutes
[0, 59]
[0, 999999999]
seconds
[0, 59]
[0, 999999999]
frac_secs
>=0,最多存6位有效数字
>=0,最多存6位有效数字
ISO各格式元素输出结果,如表6所示。
表6 输出结果示例表 输入
输出
说明
'P01Y02M'
+01-02
-
'P24M'
+02-00
-
'-P0Y123M'
-10-03
-
'-P9999Y999999M'
错误
MONTH域超出范围(<=99999)
'P9999DT2000H'
+00-00
YEAR和MONTH域未指定值,忽视其他域的值
'-P012.3M23D'
错误
每个域只能为整数
'-P 12M'
错误
ISO格式中不允许空格
' -P12M '
-01-00
允许首尾存在空格
'-P12M33Y'
错误
YEAR域不能在MONTH域之后
'1233-0'
+1233-00
-
'- 1233 - 2'
-1233-02
SQL格式中允许空格
' +00-000003'
+00-03
允许首尾空格,域可以以多个0开始
'12332-1'
错误
YEAR域超出范围(<=9999)
'12-12'
错误
SQL格式中MONTH域超出范围(<=11)
'223'
错误
不满足格式要求
说明:
- 所有域的取值都是非负整数。
- 在TO_YMINTERVAL函数中,即使指定了DAY、HOUR、MINUTE等域的值,它们仍将会被忽略,TO_YMINTERVAL函数仅关注YEAR和MONTH域指定的值。
- 在ISO格式中域的值必须按顺序指定,例如YEAR域不能出现在MONTH域之后。
- 若指定时间域指示符T,其后面至少出现一个时间域。
- 当指定FRAC_SEC域时,SECOND也必须被指定,且它们之间不允许存在空格。
示例1:
返回当前时间开始2年零5个月后的时间。
SELECT (SYSDATE) + TO_YMINTERVAL('02-05') from SYS_DUMMY; (SYSDATE) + TO_YMINTERVAL('02-05') ---------------------------------- 2021-08-13 15:45:53 1 rows fetched.
示例2:
返回至2018年12月31日已经为公司工作1年零3个月的员工编号及姓名。
--创建表employee。 CREATE TABLE employee(employee_id INT NOT NULL,first_name VARCHAR(10),last_name VARCHAR(10), hire_date DATETIME);
--插入几条数据。 INSERT INTO employee(employee_id,first_name,last_name,hire_date) VALUES(1001,'Alice','BROWN','2017-06-20 12:00:00'); INSERT INTO employee(employee_id,first_name,last_name,hire_date) VALUES(1002,'BOB','Smith','2017-10-20 12:00:00'); INSERT INTO employee(employee_id,first_name,last_name,hire_date) VALUES(1003,'ALAN','Jones','2017-05-10 12:00:00'); --提交事务。 COMMIT;
--至2018年12月31日已经为公司工作1年零3个月的员工。 SELECT employee_id, first_name, last_name FROM employee WHERE hire_date + TO_YMINTERVAL('01-03') <= DATE '2018-12-31' ORDER BY employee_id; EMPLOYEE_ID FIRST_NAME LAST_NAME ------------ ---------- ---------- 1001 Alice BROWN 1003 ALAN Jones 2 rows fetched.
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」关注作者【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。评论