Dan,
I did not know of the JDBC escape functions. These work indeed with
Derby, however the MySQL JDBC driver does not seem to support them (we
need to run our queries on both Derby and MySQL).
I know that this is not the best place to ask, but does anyone happen to
know more about the status of JDBC escape functions in MySQL?
Robert
Daniel John Debrunner wrote:
Robert Enyedi wrote:
Michael,
As far as I know, year 0 in MySQL is actually 1 AD. However, it doesn't
necessarily matter when that year is when you have the comparability of
the results.
What matters that this function in combination with its reverse,
FROM_DAYS, allows easily for one to compute things like:
- are both timestamps in the same day? TO_DAYS(tstamp1) = TO_DAYS(tstamp2)
I think this will have the same result:
DATE(tstamp1) = DATE(tstamp2)
- how many days are between the two dates? TO_DAYS(date1) - TO_DAYS(date2)
Use the TIMESTAMPDIFF function (see below)
- what is the date if one adds X days to a specified date?
FROM_DAYS(TO_DAYS(date1) + X)
Use the TIMESTAMPADD function.
I'm not specifically in love with this function either, but what other
alternatives does Derby offer to make these computations?
DATE(number) looks the same as FROM_DAYS.
http://db.apache.org/derby/docs/10.1/ref/rrefdatefunc.html
For the others you can use the TIMESTAMPADD and TIMESTAMPDIFF JDbC
escape functions, see the bottom of this page:
http://db.apache.org/derby/docs/10.1/ref/rrefjdbc88908.html
Note these are JDBC escape functions, thus they have to be called using
{fn }, here's an example from that page.
{fn TIMESTAMPADD( SQL_TSI_MONTH, 1, CURRENT_TIMESTAMP)}
Returns a timestamp value one month later than the current timestamp.
Dan.
