db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Robert Enyedi <robert.eny...@intland.com>
Subject Re: MySQL TO_DAYS() emulation in Derby
Date Mon, 18 Sep 2006 08:55:57 GMT
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.
>
>
>
>
>
>
>   


Mime
View raw message