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