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

Question

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?

David S. · Accepted Answer · 2016-06-24 19:20:20Z

0

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.

NNN days

NNN hours

NNN minutes

NNN.NNNN seconds

NNN months

NNN years

start of month

start of year

start of day

weekday N

unixepoch

localtime

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

answered Jun 24, 2016 at 19:20

David S.

6,7051 gold badge30 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

skymatt70 Over a year ago

David, thanks for such a well documented answer! It still does not solve the daylight savings time problem where 2 days per year are not 24 hours and when crossing these boundaries the offset will not be the same for the start and end. I'm still going to have to handle it day by day getting start and end epoch from NSDate, but using the calculated offset may be a great solution for most.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related