MySQL Function Compatabiliity: ADDDATE and DATEDIFF

[Yonas]

Hi,

We have a bunch of SQL that we like to run on both mysql and h2.
However we often need to check which database is being used when we
call some of the date functions.

For us, a change like the following seems to make the ADDDATE and
DATEDIFF function mostly compatible with MySQL.

If you wanted to integrate this into your tree, we would not need to
keep our own tree..... The code below is conceptual only as it will
not correctly dispatch to the two functions.

If you like I could add the remaining code and make a full diff.

The gist of our issue is that we need to be able to do both adddate
and datediff on H2 and MySQL but the parameters are different.

Y.



Index: src/main/org/h2/expression/Function.java
===================================================================
--- src/main/org/h2/expression/Function.java (revision 1817)
+++ src/main/org/h2/expression/Function.java (working copy)
@@ -89,7 +89,7 @@
DAY_OF_MONTH = 105, DAY_OF_WEEK = 106, DAY_OF_YEAR = 107,
HOUR = 108, MINUTE = 109, MONTH = 110, MONTH_NAME = 111,
NOW = 112, QUARTER = 113, SECOND = 114, WEEK = 115, YEAR
= 116, CURRENT_DATE = 117, CURRENT_TIME = 118,
CURRENT_TIMESTAMP = 119, EXTRACT = 120, FORMATDATETIME =
121, PARSEDATETIME = 122,
- ISO_YEAR = 123, ISO_WEEK = 124, ISO_DAY_OF_WEEK = 125;
+ ISO_YEAR = 123, ISO_WEEK = 124, ISO_DAY_OF_WEEK = 125,
ADDDATE=126;

public static final int DATABASE = 150, USER = 151, CURRENT_USER
= 152, IDENTITY = 153, AUTOCOMMIT = 154,
READONLY = 155, DATABASE_PATH = 156, LOCK_TIMEOUT = 157;
@@ -1002,8 +1002,16 @@
case DATE_ADD:
result = ValueTimestamp.getNoCopy(dateadd(v0.getString(),
v1.getInt(), v2.getTimestampNoCopy()));
break;
+ case ADDDATE:
+ result = ValueTimestamp.getNoCopy(dateadd
("DAY", v1.getInt(), v2.getTimestampNoCopy()));
+ break;
case DATE_DIFF:
+ if (args.length == 2) {
+ result = ValueLong.get(datediff("DAY",
v1.getTimestampNoCopy(), v2.getTimestampNoCopy()));
+ } else {
result = ValueLong.get(datediff(v0.getString(),
v1.getTimestampNoCopy(), v2.getTimestampNoCopy()));
+ }
+
break;
case EXTRACT: {
int field = getDatePart(v0.getString());

[Thomas Mueller]

Hi,

Thanks for the patch, however it is incomplete. You would need to add

addFunction("ADDDATE", ADDDATE, ...);

also DATE_DIFF is incomplete. Could you please provide a complete
test, and a few test cases? See also
http://www.h2database.com/html/build.html#providing_patches

Regards,
Thomas