CODEWITHSUNDEEP
iosswiftsqlitefmdbnstimezone
skymatt70
skymatt70

Reputation: 63

SQLite on iOS date functions using system timezone even though default timezone for the app has been set

I'm working on an app in which users can log in and select a site which may be in a different time zone. Because the app is showing data that is relevant to that site I've decided to set the default timezone for the app to be the site timezone via NSTimeZone.setDefaultTimeZone. This works great except when I select data out of our sqlite db via FMDB (I don't think FMDB has anything to do with it) and use strftime with 'localtime'. While our data is stored by the minute in UTC using epochs, we often need to show summations by day, weekday or month in the local time zone. For example to get averages by weekday: 

select strftime('%w',datetime(epoch,'localtime')),avg(value) 
    from values 
    where siteId = 'x'  
    group by 1 
    order by 2 desc

The 'localtime' that it's using is the system local time and not the default time zone for the app where all NSDate calls respect the default time zone. There does not to be any other timezone options for strftime other than localtime and UTC and the current work arounds are pretty slow requiring multiple SQL roundtrips where this should easily be handled in 1 query as above.

Can anyone tell me how sqlite on iOS determines 'localtime'? Is there a way to force it to use a different i.e. defaultTimeZone?

Upvotes: 0

Views: 815

Answers (1)

David S.
David S.

Reputation: 6705

As you have seen, SQLite doesn't use NSDate or the app's local timezone. The datetime function converts with a Modifier. Say you had a DB that stores as GMT (I think this is the case for your app):

sqlite> create table mytable (id int, time datetime);
sqlite> insert into mytable values (1, CURRENT_TIMESTAMP);
sqlite> select time from mytable;
2016-06-24 19:05:36  <-  THIS IS GMT
sqlite> select datetime(time, 'localtime') from mytable;
2016-06-24 15:05:36  <- THIS IS LOCAL TIME

In this example (and yours) 'localtime' is the Modifier. From the sqlite docs:

Modifiers

The time string can be followed by zero or more modifiers that alter date and/or time. Each modifier is a transformation that is applied to the time value to its left. Modifiers are applied from left to right; order is important. The available modifiers are as follows.

  1. NNN days
  2. NNN hours
  3. NNN minutes
  4. NNN.NNNN seconds
  5. NNN months
  6. NNN years
  7. start of month
  8. start of year
  9. start of day
  10. weekday N
  11. unixepoch
  12. localtime
  13. utc

So you cannot directly convert to the local value. However, because you can use these modifers your app can get your local GMT offset from NSDate:

if let myZone = NSTimeZone(abbreviation: "EST")
{
    NSTimeZone.setDefaultTimeZone(myZone)
    var offset = (myZone.secondsFromGMT)/3600 as Int
    var offsetModifer = "\(offset) hours"
}

Then you can execute your sqlite query as so (building the query using offsetModifer which translates to -4 hours in the example here:

sqlite> select datetime(time, '-4 hours') from mytable;
2016-06-24 15:05:36

Upvotes: 0

Related Questions