1

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: sqlite datetime data type with iphone NSdate?

Community
  • 1
  • 1
Dimitris
  • 13,480
  • 17
  • 74
  • 94

5 Answers5

2

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.

Community
  • 1
  • 1
teabot
  • 15,358
  • 11
  • 64
  • 79
  • I try this and it doesn't make a difference. Also it has to be relative, so I don't want to set it to GMT+1 and get wrong timestamps if someone plays in China... – Dimitris Jun 19 '09 at 16:20
  • I am not suggesting that you set it to GMT+1. All time stamps in SQLite seem to be fixed to GMT, so the NSDateFormatter you describe must also be synchronized to the same time zone (GMT). The NSDate derived from the formatter should be time zone independent and thus transformable into any other time zone - be it GMT+1 or Beijing. – teabot Jun 19 '09 at 23:13
0

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.

Grouchal
  • 9,756
  • 6
  • 34
  • 46
  • Thanks for the tip, but since the app is already made i'd hate to change it. I'll keep it in mind for my next one :) – Dimitris Jun 19 '09 at 16:16
0

I have just had to fix this problem also, and teapot's answer is the correct one. The timestamp created by sqlite is in GMT, so when you read it from the database you need to tell your date formatter to read it in GMT also. The resulting NSDate will be a date correct in whatever timezone you are in. The way you say you have corrected it, by adding 1 hour, will eventually fail on you. Anyone using the app in the GMT timezone will see all times +1 hour. Anyone in other timezones will see a completely different time offset. (I know this is years old, but it has just helped me).

Darren
  • 10,182
  • 20
  • 95
  • 162
0

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

  • I remember trying his suggestion back then, but it didn't work... If people agree that teabot's method works, I will accept his answer as correct to help others. I'll also try to do implement it myself again too when I get the time. – Dimitris Sep 27 '09 at 00:49
-1

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.

Dimitris
  • 13,480
  • 17
  • 74
  • 94
  • This is not the correct way to deal with this problem and will cause you problems when your users are in different timezones. – Darren May 08 '12 at 10:51