You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Robert Enyedi <ro...@intland.com> on 2006/09/15 14:15:20 UTC
MySQL TO_DAYS() emulation in Derby
The TO_DAYS() function returns the number of days since year 0. Did
someone successfully port this functionality to Derby? Constructing a
Java function for this is not as straightforward as it looks.
Regards,
Robert
Re: MySQL TO_DAYS() emulation in Derby
Posted by "Lance J. Andersen" <La...@Sun.COM>.
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.
>>
>>
>>
>>
>>
>>
>>
>
Re: MySQL TO_DAYS() emulation in Derby
Posted by Robert Enyedi <ro...@intland.com>.
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.
>
>
>
>
>
>
>
Re: MySQL TO_DAYS() emulation in Derby
Posted by Daniel John Debrunner <dj...@apache.org>.
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.
Re: MySQL TO_DAYS() emulation in Derby
Posted by Robert Enyedi <ro...@intland.com>.
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)
- how many days are between the two dates? TO_DAYS(date1) - TO_DAYS(date2)
- what is the date if one adds X days to a specified date?
FROM_DAYS(TO_DAYS(date1) + X)
I'm not specifically in love with this function either, but what other
alternatives does Derby offer to make these computations?
Regards,
Robert
Michael Segel wrote:
> On Friday 15 September 2006 7:15 am, Robert Enyedi wrote:
>
>> The TO_DAYS() function returns the number of days since year 0. Did
>> someone successfully port this functionality to Derby? Constructing a
>> Java function for this is not as straightforward as it looks.
>>
>> Regards,
>> Robert
>>
> Why?
>
> When was year 0?
>
> Just because you see a function in MySQL, doesn't mean that its a good thing
> or that it has value.
>
> Everytime you "add" a function, you're increasing the footprint of Derby.
> To some this isn't an issue. To others who wish to embed Derby within an
> Applet/Servlet/whatever... This is not a good thing.
>
>
>
Re: MySQL TO_DAYS() emulation in Derby
Posted by Michael Segel <ms...@segel.com>.
On Friday 15 September 2006 7:15 am, Robert Enyedi wrote:
> The TO_DAYS() function returns the number of days since year 0. Did
> someone successfully port this functionality to Derby? Constructing a
> Java function for this is not as straightforward as it looks.
>
> Regards,
> Robert
Why?
When was year 0?
Just because you see a function in MySQL, doesn't mean that its a good thing
or that it has value.
Everytime you "add" a function, you're increasing the footprint of Derby.
To some this isn't an issue. To others who wish to embed Derby within an
Applet/Servlet/whatever... This is not a good thing.