Re: NSTimestamp mapped to DATE for mysql
Re: NSTimestamp mapped to DATE for mysql
- Subject: Re: NSTimestamp mapped to DATE for mysql
- From: Lachlan Deck <email@hidden>
- Date: Sat, 19 Jan 2008 23:32:06 +1100
Hi John,
see if this helps...
On 18/01/2008, at 9:28 PM, John Pollard wrote:
On 18 Jan 2008, at 03:35, Lachlan Deck wrote:
On 18/01/2008, at 2:44 AM, John Pollard wrote:
Many thanks for your reply. I do do all the default timezone
setting in my application, client and server side. However, I
found SimpleDateTimes used for conversions don't use the system
timezone and you also have to set it on these explicitly. I
currently set it to London/Europe.
Hmm. Firstly, are you certain you're setting the runtime TimeZone for
java correctly? Because SimpleDateFormat will use a Calendar
initiated with TimeZone.getDefault(), and Locale for that matter.
How are you doing it? Here's what I'm doing... (Perhaps, if you're
using Project WOnder there's a simple property for this... if so,
wait for a 'sigh, WOnder blah blah...' from Anjo ;-)
protected void initialiseTimeZone()
{
String tz;
if ( LOG.isDebugEnabled() )
LOG.debug( "testing for default timezone property:" +
ISHApplicationDefaultTimeZone );
tz = NSProperties.getProperty( ISHApplicationDefaultTimeZone, null );
if ( tz != null )
{
NSTimeZone defaultTimeZone;
if ( LOG.isDebugEnabled() )
LOG.debug( "setting default timezone:" + tz );
defaultTimeZone = NSTimeZone.timeZoneWithName( tz, true );
if ( defaultTimeZone == null )
throw new IllegalStateException( "No such timezone [" + tz + "].
Aborting. Please rectify property " + ISHApplicationDefaultTimeZone );
// Set both default NS and java.util TimeZone.
NSTimeZone.setDefaultTimeZone( defaultTimeZone );
TimeZone.setDefault( NSTimeZone.getDefault() );
}
else if ( LOG.isDebugEnabled() )
LOG.debug( "no default timezone property specified" );
}
Secondly, setting it to London/Europe for parsing/formatting dates is
your problem. This is the rule of thumb that works for working with
dates (as opposed to datetimes): 'Dates are dates are dates. They do
not have timezones. They are like constants. They don't change.' My
birthdate, for example, doesn't change should I travel to somewhere
with a different timezone.
See below for a further explanation...
When my code uses GMT, the date range query looks better as no
hour is added for BST as you said. However, once the query RESULT
data gets back to my WO Java Client app, the NSTimestamps have
lost an hour and hence flip to the previous day.
Yep, and that's because the time is getting truncated in your
database field because you're storing dates rather than datetimes.
Is that right?
I wouldn't expect this when using GMT because there should be no
time shifting?
Timeshifting no... time truncation, yes...
Shouldn't the dates go in with no time component and come out the
same, whether during a BST period or not?
Not quite. Let's say it's 31st March 2008 @ 12:30 AM BST and at that
instant you:
- create an in-memory timestamp, parsing user input from your above-
mentioned BST SimpleDateFormat, and
- in the GUI you show the user their input formatting the date, again
using your local timezone BST, as yyyy-MMM-dd.
So the user sees: 2008-MAR-31 because internally the NSTimestamp (or
Calendar) has a timestamp == 30th March 2008 @ 11:30 PM GMT/UTC.
Upon saving this to the database (where you're storing, I assume, a
date without time)... guess what's saved? 2008-03-30.
Your user may still see 31st for a while due to snapshot caching...
but after restarting your app, for example, the next time you display
this to the user (if formatting to a full timestamp) the user will
see 2008-MAR-30 01:00 AM.
You've, by this time, lost 23 hours from your date because you didn't
stick to GMT from GUI to database.
When I view the Date & Time panel in System Preferences, the Time
Zone says GMT (and can't be changed), which would seem to be
correct.
? In your mac Sys prefs? You'll have to click the Lock image,
provide your password etc...
Even when unlocked. I can pick a Closest City only within GMT. I am
an administrator.
I'd just keep your computer system's prefs as normal but set the java
runtime to GMT as mentioned above. That's all that's needed.
Using Europe/London as the timezone works when using mysql 5.0.18
but breaks as described below in the latest 5.0.45. When using
this, the hour is shifted in queries sent to the DB, but using
mysql 5.0.18, the right results come back.
Strange. I wouldn't have expected it to work in the past given the
above assumption about your storage of dates. (Except that maybe
now you're in daylight saving time?)
It is ok for querying dates around now, but queries of dates from
last summer break as described above, only in 5.0.45. I had to put
our live database back to 5.0.18 so it currently works ok. This
really gets me, that something has changed between versions of
mysql apparently.
You might want to check the mysql admin docs for any configuration
options or jdbc arguments available that might control any of this
behaviour.
I think using GMT is my most likely route to success, I just have
to work out why dates with no time coming off the database then
appear with a time in my app.
See above.
So before you save a timestamp to the database that's meant to be
just a date you need to create a GMT timestamp with a semantically
equivalent datetime. Something like this:
--------
/**
* NSTimestamps are always in UTC. i.e., with regards to TimeZones,
we know not from whence it came.
* Hence, the second argument provides that info.
*
* @return an NSTimestamp after adjusting for the timezone offset.
*/
public static NSTimestamp
gmtEquivalentDateTimestampFromTimestampAndZone( NSTimestamp
timestamp, TimeZone tz )
{
NSTimestamp result = null;
if ( timestamp != null )
{
TimeZone gmtTimeZone = TimeZone.getTimeZone( "GMT" );
TimeZone inputTimeZone = tz == null ? gmtTimeZone : tz;
Calendar cal = Calendar.getInstance( inputTimeZone );
cal.setTimeInMillis( timestamp.getTime() );
int year = cal.get( Calendar.YEAR );
int month = cal.get( Calendar.MONTH );
int date = cal.get( Calendar.DATE );
cal.clear();
cal.setTimeZone( gmtTimeZone );
cal.set( year, month, date );
result = new NSTimestamp( cal.getTimeInMillis() );
}
return result;
}
with regards,
--
Lachlan Deck
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden