MySQL date functions

[Boris Granveaud]

Hello,

I need to be able to execute MySQL statements on H2 for my unit tests.
And I'm blocked by the DATE_ADD(...) function.

I tried to define my own function in Java like this:

public class H2Functions {
public static Date dateAdd(Date date, String expr) {
...
}
}

CREATE ALIAS DATE_ADD FOR H2Functions.dateAdd;
SELECT DATE_ADD(now(), INTERVAL 1 DAY);

Syntax error in SQL statement SELECT DATE_ADD(NOW(), INTERVAL 1[*]
DAY) ; expected ., (, [, ::, *, /, %, +, -, ||, ~, !~, NOT, LIKE,
REGEXP, IS, IN, BETWEEN, AND, OR, ), ,; SQL statement:
SELECT DATE_ADD(now(), INTERVAL 1 DAY) [42001-164] 42001/42001

I understand that INTERVAL 1 DAY is not a string, and so my function
is not called.

Is there a mean to have a SQL request with DATE_ADD which works on
both H2 and MySQL?

Thanks.

-- 
You received this message because you are subscribed to the Google Groups H2 
Database group.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to 
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/h2-database?hl=en.

Re: MySQL date functions

[Noel Grandin]

We don't have such a facility at the moment, but it sounds like a
reasonable feature request.

We could allow you to mark a function alias as having free-form
parameters. Then it would be up to the function code to parse the
arguments.

Something like
 CREATE ALIAS DATE_ADD FREEFORM FOR H2Functions.dateAdd;

Where the method would have to be defined like this:
 public static void dateAdd(String[] params) {}

Thomas, what do you think of my proposed syntax?

On Mon, Feb 6, 2012 at 17:55, Boris Granveaud bo...@granveaud.com wrote:
 Hello,

 I need to be able to execute MySQL statements on H2 for my unit tests.
 And I'm blocked by the DATE_ADD(...) function.

 I tried to define my own function in Java like this:

 public class H2Functions {
        public static Date dateAdd(Date date, String expr) {
        ...
        }
 }

 CREATE ALIAS DATE_ADD FOR H2Functions.dateAdd;
 SELECT DATE_ADD(now(), INTERVAL 1 DAY);

 Syntax error in SQL statement SELECT DATE_ADD(NOW(), INTERVAL 1[*]
 DAY) ; expected ., (, [, ::, *, /, %, +, -, ||, ~, !~, NOT, LIKE,
 REGEXP, IS, IN, BETWEEN, AND, OR, ), ,; SQL statement:
 SELECT DATE_ADD(now(), INTERVAL 1 DAY) [42001-164] 42001/42001

 I understand that INTERVAL 1 DAY is not a string, and so my function
 is not called.

 Is there a mean to have a SQL request with DATE_ADD which works on
 both H2 and MySQL?

 Thanks.

 --
 You received this message because you are subscribed to the Google Groups H2 
 Database group.
 To post to this group, send email to h2-database@googlegroups.com.
 To unsubscribe from this group, send email to 
 h2-database+unsubscr...@googlegroups.com.
 For more options, visit this group at 
 http://groups.google.com/group/h2-database?hl=en.


-- 
You received this message because you are subscribed to the Google Groups H2 
Database group.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to 
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/h2-database?hl=en.

Re: MySQL date functions

[Thomas Mueller]

Hi,

The H2 parser doesn't currently understand INTERVAL. What you could do
is use TIMESTAMPADD, which is supported by both H2 and MySQL:

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timestampadd

http://h2database.com/html/functions.html#dateadd

I guess I will have to add the INTERVAL syntax.

 free-form parameters

I don't know... it would mean for some of the parameters the function
is responsible for parsing, but that would complicate things quite a
lot. For example:

SELECT DATE_ADD(now(), INTERVAL (X+Y+?) DAY) from ...

Who would be responsible to parse X+Y+? (a parameter). It would
require quite a lot of new API. If possible, I would try to avoid
that.

I guess it would be easier to support INTERVAL ... unit as a new
data type. Anyway it seems this is required at some point.

Regards,
Thomas

-- 
You received this message because you are subscribed to the Google Groups H2 
Database group.
To post to this group, send email to h2-database@googlegroups.com.
To unsubscribe from this group, send email to 
h2-database+unsubscr...@googlegroups.com.
For more options, visit this group at 
http://groups.google.com/group/h2-database?hl=en.