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

> This is exactly what I've been trying to do. The difficulty which I'm
> unable to overcome yet is that the timestamp returned by
> java.sql.Date.getTime() is not divisible with the number of miliseconds
> in a day (24 * 60 * 60 * 1000). Yet when I create a java.util.Date()
> instance with that timestamp, this gives me the 00:00:00 hour of the
> desired day (so no time information is in the timestamp).
> 
> This puzzles me, so that's why I decided to ask around.
> 
> Regards,
> Robert
> 
> dmclean62@comcast.net wrote:
> 
>> Java calendar and date objects work in miliseconds but I think that
>> fields marked as "date" rather than timestamp just have their
>> hour/minute/second parts set to 0. Even so, it's fairly easy to roll a
>> timestamp back to 00:00:00. You can then subtract the Date.getTime()
>> values and divide by 86,400,000 to get days.
>>
>> Or am I missing something obvious here?

When the timezone of the client is different to the timezone of the
server then these type of calculations can get interesting. Derby's JDBC
implementation notes contain some (hopefully) clear technical
descriptions of how Derby handles the some of the interaction of its SQL
date time types with JDBC's date time types. It covers the cases for

   TIMESTAMP <-> java.sql.Timestamp
   DATE <-> java.sql.Date
   TIME <-> java.sql.Time

http://db.apache.org/derby/papers/JDBCImplementation.html

There are a couple of bugs in this area as well, DERBY-889, DERBY-1811.
These are when using ResultSet.getTimestamp to perform this conversion,
TIME -> java.sql.Timestamp. Following the discussion around those issues
I will be updating that document to cover other date time conversions.

Dan.


Mime
View raw message