db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@apache.org>
Subject Re: MySQL TO_DAYS() emulation in Derby
Date Fri, 15 Sep 2006 14:12:35 GMT
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