Wrong time on SQLite timestamp on the iPhone

Question

From my iPhone app I insert some data in my sqlite database that include a date using the CURRENT_TIMESTAMP default value. Everything works great except for the fact that the time is 1 hour behind the time it should be. And that happens both on the device and the Simulator. Any sqlite settings (like current time) i can access somewhere?

If you want to see the code I am using for that you can take a look at my answer to this post: http://stackoverflow.com/questions/942188/sqlite-datetime-data-type-with-iphone-nsdate/1000959#1000959

Answer 1

It sounds as though there might be a day light savings time zone inconsistency between SQLite and the time zone that NSDateFormatter is using. It looks as though SQLite stores the datetime in GMT. Therefore you'll need to make sure that NSDateFormatter is also set to GMT. It appears as though you can do this with:

gmtFormatter.timeZone = [NSTimeZone timeZoneWithAbbreviation:@"GMT"];
[gmtFormatter setDateFormat:@"yyyy-MM-dd HH:mm:ss"]

Once you have your NSDate instance I'd expect that you could for example get a string version of it showing the correct local time using a NSDateFormatter instance that is set with the local/system time zone.

Note: I haven't actually tried this.

Answer 2

You might want to consider using Core Data which is now available in O/S 3.0 - this handles things like this automatically for you without you having to specify timezones etc - it will just use the user's defaults and keep everything consistent.

There are many other advantages - but this seems like a relevant one.

Answer 3

Well, since I couldn't find where this 1 hour difference comes from, I used the following method to fix the date. It's not a proper solution but it does exactly what i need. So in case someone else runs into the same trouble as me.. here's my fix:

NSDate *date = [[NSDate alloc] initWithTimeInterval:3600 sinceDate:[formatter dateFromString:score.datetime]];

which is just adding a one hour delay to the datetime, therefore making it correct.

Answer 4

Hello Dimitris,

I guess you better implement teabot's suggestion. The Problem comes when one timezone has wintertime and one doesnt...