解决人大金仓数据库KingbaseES DATE_ADD函数使用的问题
DATE_ADD函数是比较常用的日期时间函数，主体功能是用于对日期和时间进行加减操作。DATE_ADD(TYPE_date, INTERVALexpr unit函数功能将一个日期或时间加上指定的时间间隔，返回一个新的日期或时间。参数1： TYPE_date：要进行加减操作的日期或时间，可以是date、datetime、time、timestamp类型的参数。参数2： interval关键字加后面的
DATE_ADD函数之KingbaseES与其他数据库比较
关键字
DATE_ADD函数、KingbaseES、MySQL、人大金仓、KingbaseES
问题描述
目前KingbaseES只支持与DATE_ADD函数功能相近的ADDDATE函数，为实现KingbaseES中兼容DATE_ADD函数，且实现函数的完整功能，本文对不同数据库中的DATE_ADD函数进行调研分析，进一步完善KES中DATE_ADD函数功能。
函数介绍
DATE_ADD函数是比较常用的日期时间函数，主体功能是用于对日期和时间进行加减操作。
函数原型：
DATE_ADD(TYPE_date, INTERVAL expr unit );
函数功能：
将一个日期或时间加上指定的时间间隔，返回一个新的日期或时间。
参数说明:
参数1： TYPE_date：要进行加减操作的日期或时间，可以是date、datetime、time、timestamp类型的参数。
参数2： interval关键字加后面的内容
1. expr：指定要日期或时间加上或者减去的数值，以字符串的形式输入，可能是一个带‘-’（负号）的值。
2. unit：日期加减数值的类型，unit支持的类型包括：
|
unit支持的类型 |
描述 |
|
YEAR |
描述若干年的间隔 INTERVAL '9' YEAR |
|
MONTH |
描述若干月的间隔 INTERVAL '9' MONTH |
|
DAY |
描述若干天的间隔 INTERVAL '9' DAY |
|
HOUR |
描述若干小时的间隔 INTERVAL '9' HOUR |
|
MINUTE |
描述若干分钟的间隔 INTERVAL '9' MINUTE |
|
SECOND |
描述若干分钟的间隔 INTERVAL '9' MINUTE |
|
YEAR TO MONTH/YEAR_MONTH |
描述若干年若干月间隔 INTERVAL '10-9' YEAR TO MONTH |
|
DAY TO HOUR/DAY_HOUR |
描述若干天若干小时间隔 INTERVAL '10 9' DAY TO HOUR |
|
DAY TO MINUTE/DAY_MINUTE |
描述若干天若干分钟间隔 INTERVAL '10 9:09' DAY TO MINUTE |
|
DAY TO SECOND/DAY_SECOND |
描述若干天若干秒间隔 INTERVAL '10 9:09:50' DAY TO SECOND |
|
HOUR TO MINUTE/HOUR_MINUTE |
描述若干小时若干分钟间隔 INTERVAL '10:09' HOUR TO MINUTE |
|
HOUR TO SECOND/HOUR_SECOND |
描述若干小时若干秒间隔 INTERVAL '10:09:10' HOUR TO SECOND |
|
MINUTE TO SECOND/MINUTE_SECOND |
描述若干分钟若干秒间隔 INTERVAL '09:10' MINUTE TO SECOND |
表 1
返回值：
返回一个新的日期或时间，返回值的类型与TYPE_date类型一致。
各常用数据库对比分析
分析对比总述
各类数据库对DATE_ADD函数的支持情况见下表。
|
数据库 |
是否支持date_add函数 |
函数原型 |
|
KES |
支持 |
DATE_ADD(TYPE_date, INTERVAL expr unit ); DATE_ADD(TYPE_date, float4 ); |
|
MySQL |
支持 |
DATE_ADD(date,INTERVAL expr unit); |
|
oracle |
不支持 |
不支持 |
|
dm8 |
支持 |
DATE_ADD(date,INTERVAL expr unit); |
|
sqlserver |
不支持，相近函数为dateadd() |
DATEADD(datepart,number,date) |
|
opengauss |
暂未调研 |
暂未调研 |
表 2
对比不同数据库中该函数的功能，列举下列差异点进行示例分析。
|
对比类型 |
对比点 |
示例分析章节 |
|
输入参数1 |
输入参数1时是否加类型名称 |
2.1.1 |
|
不加参数类型的输入，系统存储的时间类型 |
2.1.2 |
|
|
输入time类型参数，不加类型名称 |
2.1.3 |
|
|
参数1输入now() |
2.1.4 |
|
|
参数1 输入NULL |
2.1.5 |
|
|
参数1输入空串 |
2.1.6 |
|
|
输入参数2 |
参数2是否支持float类型 |
2.2.1 |
|
interval后输入的数值是否可以省略单引号 |
2.2.2 |
|
|
interval后的unit部分是否可以省略 |
2.2.3 |
|
|
参数2 输入NULL |
2.2.4 |
|
|
参数2输入空串 |
2.2.5 |
|
|
特殊示例 |
对月末31日的日期类型，增加月份结果 |
2.3.1 |
表 3
示例分析
示例分析对比的数据库包括：KingbaseES、MySQL、dm8、oracle
2.1 输入参数1相关分析
2.1.1 输入参数1时是否加类型名称
KES使用手册中对于该函数的使用方法，示例显示输入参数1时需要在日期时间数据前加上日期时间类型名称，而mysql手册中使用该函数时，不需要在数据前加上类型名称。
实际示例结果可见表中，对于参数1的时间日期数据，若按时间日期标准格式输入，则可以不加上日期时间类型名称，不影响函数使用。
|
数据库 |
测试用例 |
结果 |
|
KES |
select date_add(timestamp'2022-10-24 21:30:00', interval '5' hour); |
2022-10-25 02:30:00 |
|
select date_add('2022-10-24 21:30:00', interval '5' hour); |
2022-10-25 02:30:00 |
|
|
MySQL |
select date_add(timestamp'2022-10-24 21:30:00', interval '5' hour); |
2022-10-25 02:30:00 |
|
select date_add('2022-10-24 21:30:00', interval '5' hour); |
2022-10-25 02:30:00 |
|
|
dm8 |
select date_add(timestamp'2022-10-24 21:30:00', interval '5' hour); |
2022-10-25 02:30:00.000000 |
|
select date_add('2022-10-24 21:30:00', interval '5' hour); |
2022-10-25 02:30:00.000000 |
|
|
oracle |
不支持 |
不支持 |
表 4
2.1.2 不加参数类型的输入，系统存储的时间类型
当只输入日期类型数据时，KES函数结果会补全时间部分数据。mysql中如果不对时间部分进行计算，则结果省略时间部分，若对时间部分有计算，则结果会显示时间。
|
数据库 |
测试用例 |
结果 |
|
KES |
select date_add(date'2022-10-24', interval '5' month); |
2023-03-24 00:00:00 |
|
select date_add(date'2022-10-24', interval '5' second); |
2022-10-24 00:00:05 |
|
|
select date_add('2022-10-24', interval '5' month); |
2023-03-24 00:00:00 |
|
|
MySQL |
select date_add(date'2022-10-24', interval '5' month); |
2023-03-24 |
|
select date_add(date'2022-10-24', interval '5' second); |
2022-10-24 00:00:05.000000 |
|
|
select date_add('2022-10-24', interval '5' month); |
2023-03-24 |
|
|
dm8 |
select date_add(date'2022-10-24', interval '5' month); |
2023-03-24 00:00:00.000000 |
|
select date_add('2022-10-24', interval '5' month); |
2023-03-24 00:00:00.000000 |
|
|
oracle |
不支持 |
不支持 |
表 5
2.1.3 输入time类型参数，不加类型名称
只输入time类型参数时，需要添加输入time类型关键字，否则函数结果报错，mysql中直接返回NULL。
|
数据库 |
测试用例 |
结果 |
|
KES |
select date_add(time'21:30:00', interval '5' second); |
2023-09-08 21:30:05 |
|
select date_add('21:30:00', interval '5' second); |
ERROR: invalid input syntax for type timestamp with time zone: "21:30:00" |
|
|
MySQL |
select date_add(time'21:30:00', interval '5' second); |
21:30:05.000000 |
|
select date_add('21:30:00', interval '5' second); |
NULL |
|
|
dm8 |
select date_add(time'21:30:00', interval '5' second); |
Error in line: 1 Param incompatible. |
|
select date_add('21:30:00', interval '5' second); |
1900-01-01 21:30:05.000000 |
|
|
oracle |
不支持 |
不支持 |
表 6
2.1.4 参数1输入now()
参数1输入now()，可实现对当前时间进行加减运算。
|
数据库 |
测试用例 |
结果 |
|
KES |
select date_add(now(), interval '5' day); |
2023-09-13 14:15:38.582752 |
|
MySQL |
select date_add(now(), interval '5' day); |
2023-09-13 06:11:27 |
|
dm8 |
select date_add(now(), interval '5' day); |
2023-09-13 14:11:56.000000 |
|
oracle |
不支持 |
不支持 |
表 7
2.1.5 参数1 输入NULL
参数1输入为NULL，则函数结果返回NULL。
|
数据库 |
测试用例 |
结果 |
|
KES |
select date_add(null, interval '5' day); |
返回空 |
|
MySQL |
select date_add(null, interval '5' day); |
NULL |
|
dm8 |
select date_add(null, interval '5' day); |
NULL |
|
oracle |
不支持 |
不支持 |
表 8
2.1.6 参数1输入空串
参数1输入空串，KES中DATE_ADD函数结果显示ERROR，mysql中函数返回NULL。
|
数据库 |
测试用例 |
结果 |
|
KES |
select date_add('', interval '5' day); |
ERROR : invalid input syntax for type timestamp with time zone: "" |
|
MySQL |
select date_add('', interval '5' day); |
NULL |
|
dm8 |
select date_add('', interval '5' day); |
NULL |
|
oracle |
不支持 |
不支持 |
表 9
2.2 输入参数2相关分析
2.2.1 参数2是否支持float类型
KES中DATE_ADD支持两种形式的参数输入，参数2可以输入带interval关键字的表达式，也可以输入类型为float4的数值。其他数据库不支持。
|
数据库 |
测试用例 |
结果 |
|
KES |
select date_add(date'2022-10-24', 520); |
2024-03-27 |
|
select date_add(timestamp'2022-10-24 21:30:00', 520); |
2024-03-27 21:30:00 |
|
|
MySQL |
select date_add(date'2022-10-24', 520); |
ERROR |
|
select date_add(timestamp'2022-10-24 21:30:00', 520); |
ERROR |
|
|
dm8 |
select date_add(date'2022-10-24', 520); |
Error in line: 1 Param incompatible. |
|
select date_add(timestamp'2022-10-24 21:30:00', 520); |
Error in line: 1 Param incompatible. |
|
|
oracle |
不支持 |
不支持 |
表 10
2.2.2 interval后输入的数值是否可以省略单引号
KES中，输入参数时，interval后面的数值需要按字符串输入，不可直接输入数值，否则函数结果会报错。mysql中可以直接输入数值。
|
数据库 |
测试用例 |
结果 |
|
KES |
select date_add(date'2022-10-24', interval 5 day); |
ERROR |
|
select date_add('2022-10-24', interval 5 day); |
ERROR |
|
|
MySQL |
select date_add(date'2022-10-24', interval 5 day); |
2022-10-29 |
|
select date_add('2022-10-24', interval 5 day); |
2022-10-29 |
|
|
dm8 |
select date_add(date'2022-10-24', interval 5 day); |
ERROR |
|
select date_add('2022-10-24', interval 5 day); |
ERROR |
|
|
oracle |
不支持 |
不支持 |
表 11
2.2.3 interval后的unit部分是否可以省略
KES中，DATE_ADD函数interval后面数值字符串后的unit部分（及时间日期类型）可以省略，省略时，默认interval后面的数值字符表示要增加的秒数。
|
数据库 |
测试用例 |
结果 |
|
KES |
select date_add(date'2022-10-24', interval '5'); |
2022-10-24 00:00:05 |
|
select date_add('2022-10-24', interval '5'); |
2022-10-24 00:00:05 |
|
|
MySQL |
select date_add(date'2022-10-24', interval '5'); |
ERROR |
|
select date_add('2022-10-24', interval '5'); |
ERROR |
|
|
dm8 |
select date_add(date'2022-10-24', interval '5'); |
ERROR |
|
select date_add('2022-10-24', interval '5'); |
ERROR |
|
|
oracle |
不支持 |
不支持 |
表 12
2.2.4 参数2 输入NULL
参数2输入为NULL，KES的DATE_ADD函数返回NULL，mysql的DATE_ADD函数会报错。
|
数据库 |
测试用例 |
结果 |
|
KES |
select date_add(date'2022-10-24', NULL); |
NULL |
|
MySQL |
select date_add(date'2022-10-24', NULL); |
ERROR |
|
dm8 |
select date_add(date'2022-10-24', NULL); |
NULL |
|
oracle |
不支持 |
不支持 |
表 13
2.2.5参数2输入空串
参数2输入空串时，函数无法正常工作。
|
数据库 |
测试用例 |
结果 |
|
KES |
select date_add(date'2022-10-24',''); |
ERROR |
|
MySQL |
select date_add(date'2022-10-24',''); |
ERROR |
|
dm8 |
select date_add(date'2022-10-24',''); |
NULL |
|
oracle |
不支持 |
不支持 |
表 14
2.3 特殊示例分析
2.3.1 对月末31日的日期类型，增加月份结果
使用DATE_ADD函数对月份最后一日进行加月份的操作，当遇到31日时，KES会将日期正确显示到下一月的1日；mysql显示的结果比实际日期结果少一日。
|
数据库 |
测试用例 |
结果 |
|
KES |
SELECT DATE_ADD('2024-03-30',INTERVAL '1' MONTH) AS d1,DATE_ADD('2024-03-31',INTERVAL '1' MONTH) AS d2; |
d1:2024-04-30 00:00:00 d2:2024-05-01 00:00:00 |
|
MySQL |
SELECT DATE_ADD('2024-03-30',INTERVAL '1' MONTH) AS d1,DATE_ADD('2024-03-31',INTERVAL '1' MONTH) AS d2; |
d1:2024-04-30 d2:2024-04-30 |
|
dm8 |
SELECT DATE_ADD('2024-03-30',INTERVAL '1' MONTH) AS d1,DATE_ADD('2024-03-31',INTERVAL '1' MONTH) AS d2; |
d1:2024-04-30 00:00:00.000000 d2:2024-04-30 00:00:00.000000 |
|
oracle |
不支持 |
不支持 |
表 15
结论
通过上述章节的对比分析，可总结出在以上所提各种差异点中，KES的DATE_ADD函数功能如下：
- 参数1相关：
- KES使用手册中对于该函数的使用方法，示例显示输入参数1时需要在日期时间数据前加上日期时间类型名称；
- 当只输入日期类型数据时，KES函数结果会补全时间部分数据；
- 只输入time类型参数时，需要添加输入time类型关键字，否则函数结果报错；
- 参数1输入now()，可实现对当前时间进行加减运算；
- 参数1输入为NULL，则函数结果返回NULL。
- 参数2相关：
- KES中DATE_ADD支持两种形式的参数输入，参数2可以输入带interval关键字的表达式，也可以输入类型为float4的数值。其他数据库不支持；
- KES中，输入参数2时，interval后面的数值需要按字符串输入，不可直接输入数值，否则函数结果会报错；
- KES中，DATE_ADD函数interval后面数值字符串后的unit部分（及时间日期类型）可以省略，省略时，默认interval后面的数值字符表示为要增加的秒数。其他数据库不可省略。
- 参数2输入为NULL，KES的DATE_ADD函数返回NULL，mysql的DATE_ADD函数会报错。
- 参数2输入空串时，函数无法正常工作。
- 特殊示例
- 使用DATE_ADD函数对月份最后一日进行加月份的操作，当遇到31日时，KES会将日期正确显示到下一月的1日，mysql显示的结果比实际日期结果少一日。
有“AI”的1024 = 2048，欢迎大家加入2048 AI社区
更多推荐
17
0- 0
已为社区贡献13条内容
所有评论(0)