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:59:40 GMT
Donald,

Thanks for the code snippet. This is also what I tried to do. However, a 
modification is required so that the timezone is explicitly set:

Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("UTC"));

Otherwhise the miliseconds will be those from your own timezone (which 
most probably differs from UTC) and constructing a new date object will 
use the UTC timestamp correctly.

Also for the java.sql.Date type the code can be simplified to:

Calendar cal = Calendar.setTimeInMillis(dateObject.getTime())

Regards,
Robert

dmclean62@comcast.net wrote:
> Ok, then your best bet is probably to explicitly zero out the non-date parts:
>
> Calendar cal = Calendar.getInstance();
> cal.setTime(yourDateObject);
>
> cal.set(Calendar.HOUR_OF_DAY, 0);
> cal.set(Calendar.MINUTE, 0);
> cal.set(Calendar.SECOND, 0);
> cal.set(Calendar.MILLISECOND, 0);
>
> Donald
>
>  -------------- Original message ----------------------
> From: Robert Enyedi <robert.enyedi@intland.com>
>   
>> 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?
>>>
>>> Donald
>>>
>>>  -------------- Original message ----------------------
>>> From: Robert Enyedi <robert.enyedi@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?
>>>>     
>>>>         
>>>   
>>>       
>
>
>
>   


Mime
View raw message