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显示的结果比实际日期结果少一日。
