Hi John,
On 17/01/2008, at 2:53 AM, John Pollard wrote:
We have tables with mysql DATE type fields and EO attribute types
of NSTimestamp.
After upgrading mysql from 5.0.18 to 5.0.45, the queries are no
longer matching against dates correctly.
For recent dates the date in the query sent to the database has no
time component, such as: 2008-01-01 00:00:00
But, for dates relating to British Summer Time, they appear in the
log as: 2007-08-01 01:00:00
So a 1 hour shift has been applied to the time in the query for
dates within BST.
This is because your default TZ in your application is not GMT.
You'll have more fun with GMT.
Using mysql 5.0.18 this has/is working. With mysql 5.0.45 it no
longer works, the queries relating to BST don't match the dates
held on the datebase (just dates with no time).
You should double check that the environment settings for mysql
haven't changed to do with dates.
I think the cleanest solution would be if the various WO layers
(mysql adaptor?) didn't add any time component to the query, but I
would be very interested in any suggestions for resolving this in
any way.
There's been lots of discussion about this over the years.
The most foolproof seems to be using GMT for storage and deciding in
the gui what timezone to display as using a formatter. That means,
besides setting mysql's timezone to GMT, setting your application's
default timezone to GMT in your app's constructor.
However, when it comes to plain old dates, where no time component is
relevant (e.g., a birthdate is a birthdate unless you're recording
what time you were born), then timeshifts will cause you pain on
reconversion back to NSTimestamps on fetch back from the database
because what you thought you saved was truncated. GMT will be fine.
with regards,
--
Lachlan Deck