db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Lance J. Andersen" <Lance.Ander...@Sun.COM>
Subject Re: MySQL TO_DAYS() emulation in Derby
Date Mon, 18 Sep 2006 13:22:37 GMT
MySQL does support JDBC escape functions in their JDBC driver. 

Robert Enyedi wrote:
> 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