Handling timezones in Flutter and SQLite

The problem

One of the goals of Doshboard is to allow travelers, such as digital nomads, to make finance tracking more precise and convenient. One aspect of it is proper timezone handling.

It is a frequent situation for a nomad to have a bank account in their home country, and as usual with banking apps - they assume that you only view transactions from that country. 'Abroad' is a word bankers seem not to know. And what your average budgeting app does when you log a transaction - it uses your local timezone, but you are referencing the one your bank app gives you.

The problem is similar to the one calendar apps have when you add an event that was timed by a timezone of a third party. Here transaction is your event, and bank app is a third party. Here is an example:

1. A bank account is in Europe, so the transaction is displayed in UTC+1
2. You are somewhere in Australia, so your local time is UTC+10
3. You write your salary with a literal time from your bank app, and the app treats it as your local time
4. You go back to Europe, and your salary jumps to the previous month because it landed before 9 A.M. on the 1st

But there is more, you don't even need to travel. Say you have 2 bank accounts, one in the US, another one in Australia. And you want to compare their monthly statements between each other, what will you see? They simply cannot have the same definition of a day, week, or month because for each of those locations those were different moments in time. And if you want to compare it to a bank statement? Good luck.

A 'day' on a global scale is an illusion. It only makes sense for a given location.

At the same time for the list of all transactions - you want an absolute ordering, which does exist. The point is - if you want to be precise with your money - and your world is not limited to a single country - you need to think about timezones. Like, for example, authors of ISO 20022 that governs modern SWIFT infrastructure did.

So today we'll take a look at the stack of my app, and see if it is ready for the timezone handling on data and domain layer out of the box in order to, hopefully, avoid a big migration later, when the UI for it will finally come to be. The stack for Doshboard is SQLite, Drift and Flutter.

Here are the data layer use cases I can think of that should ensure the possibility of handling all the situations I plan for:

1. Associate a date-time value of a single row with a timezone value
2. Sort by datetime column correctly in absolute terms. All transactions happened in some order in reality, the order should stay true in my app.
3. Filter by ranges with second precision across timezones. e.g. an 'all transaction' list filtered by dates
4. Aggregate by year, quarter, month, day or week in a particular timezone. e.g. Statement from a single account

Out of scope for now, since it requires a different solution:

• Have a floating 'wall clock' type for a given locality that adjusts for DST. e.g. scheduled payments.

The out of the box state

SQLite

SQLite is pretty conservative in terms of data types it supports, so there is no dedicated datetime type. Instead it proposes 3 options to store it:

• TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
• REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
• INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

Out of these 3 only TEXT supports timezones, but only nominally. See, because it is a text, all the sorting (use case #2) is done lexicographically. Meaning it does compare dates, but only if they are all normalized to the same timezone. Take '2000-01-01 12:00:00.000Z' and '2000-01-01 13:00:00.000+03:00': the second one is actually earlier (it is 10:00 UTC), but lexicographically it sorts after the first.

For use case #3 - only REAL may have issues with matching exact sub-second precision dates due to floating point, but it shouldn't be a problem for seconds-level range queries.

For use case #4 it looked to me that TEXT is better for ranges definitions, but it turns out that it makes no difference for SQLite time functions what type to use, except for the timediff, which doesn't accept timestamps. The relevant functions will do the date range determination properly, they will use the local timezone for that though, so it partially achieves the goal, and doesn't weigh much in deciding what type to pick. What neither one does is calculating the range boundaries for a third timezone, so we have to do it in code.

What we lack entirely, is an association of a date to what timezone it belongs to. The usecase #1. And since none of the built-in representations can carry it without breaking use case #2, the timezone has to be a separate column. Where that column lives is a modeling question of its own - as we will see, it doesn't even have to be in the same table as the datetime.

Drift

Drift has support for 2 out of 3 possible types, TEXT and INTEGER. There Simon recommends using TEXT in part

due to ... timezone awareness.

It is timezone aware as we discussed above, but using this timezone awareness breaks sql sorting, and as we will see later - we can't use all of that awareness in app anyway due to the DateTimes limitation.

At the same time the integer representation saves a bit of space, makes it harder to corrupt the data (in text representation a converter may change the format silently which will break sorting), and eliminates potential errors of saving a localized datetime instead of a UTC one.

Dart

DateTime class has a major limitation for what I am trying to achieve here: it is only aware of 2 timezones. UTC and Local. It cannot be in a timezone of that banking app we talked about at the beginning, for example. That is why this model is also not suitable for use in the app.

The solution

So what do we need to change to make it all work?

SQLite

Every time-zoned datetime will be represented by 2 columns. In some cases you'll want the 2 to stay together in the same table, though in other cases you may want to spread them across different tables, like in my case where the timezone is a property of the account for bank accounts, or of the account operation for cash:

CREATE TABLE accounts (
    -- ... other columns ...
    tz TEXT -- IANA name, e.g. 'Australia/Sydney'; NULL for cash accounts
);

CREATE TABLE account_operations (
    -- ... other columns ...
    tz TEXT -- set only for operations on cash accounts, NULL otherwise
);

CREATE TABLE transactions (
    -- ... other columns ...
    time INTEGER NOT NULL, -- Unix time, always UTC
    credit_operation_id INTEGER, -- each operation belongs to an account
    debit_operation_id INTEGER
);

A transfer between two accounts in different timezones is a single instant that can land on two different statement dates - each leg gets the civil date of its own account. One datetime, two timezones.

SQLite cannot express a CHECK constraint across tables, so the rule that exactly one of the two timezones is set has to be enforced in code.

When the pair does live side by side in one table and is nullable, keep the columns in sync with:

CHECK ((started_at IS NULL) = (started_at_tz IS NULL))

The instant paired with its timezone covers the first 3 use cases. For the 4-th the timezone column tells us which calendar to bucket by - I will calculate the UTC boundaries of the period in that timezone in Dart, since, as we saw, SQLite's date functions can only do that for the local one. One more thing that I considered is normalizing the timezone column by moving it into its own table and using an id. A bit of space saved, but having a surrogate ID for a data that is static by itself seems silly. I could create a single column table without an id in order to enforce the FK constraint to validate values inserted into this column, but I really don't see my app spitting garbage into this column, and the effort of synchronising that hypothetical table with every update of a timezone package in dart sounds like too much. So I'll leave it at that.

Dart

First I need a timezone aware DateTime class. My initial consideration was to use TZDateTime from timezone package as we need it anyway for the database of timezone names the package provides. It is not a good idea to use it as a model though. For one - it is tightly coupled with that timezone database from the package, to an extent that you need a database connection in order to instantiate it. The other, minor thing, is that all my models are immutable, while this one isn't. And in general making an external object your model definition leads to coupling with that external library, unless you copy the model over into your codebase. This is why we will make our own time-zoned date time model:

sealed class ZonedDateTime
    with _$ZonedDateTime
    implements Comparable<ZonedDateTime> {
  const ZonedDateTime._();
    @Assert('instant.isUtc', 'instant must be UTC')
    @Assert(
        'instant.millisecond == 0 && instant.microsecond == 0',
        'instant must have second precision for a round trip equality',
    )
    factory ZonedDateTime({
        required DateTime instant,
        required String timeZoneId,
    }) = _ZonedDateTime;
}

Most operations just take the timezone of the account they belong to. Cash, and the defaults for the timezone selection dropdown, need the device's current timezone though - for that we are going to use the flutter_timezone package.

You need to decide how you will distribute this value across the app. In my case I get the value once during app initialization and distribute it with a singleton to keep it simple, but you may want to add a reinitialization to an AppLifecycleListener(onResume: ...) if you want to make sure it survives frequent border crossings.

// device_timezone.dart
abstract final class DeviceTimeZone {
  static String? _id;

  static String get id =>
      _id ?? (throw StateError('DeviceTimeZone has not been initialized'));

  static void initialize(String id) => _id = id;
}

// main.dart
DeviceTimeZone.initialize(
    (await FlutterTimezone.getLocalTimezone()).identifier,
);

How far into the UI the ZonedDateTime goes? Should the ZonedDateTime ever be converted into DateTime, and if so - where?

For now, I am keeping the UI that is only aware of a local timezone, so I am plumbing the ZonedDateTime all the way to the UI where it is converted to DateTime for display, while writes use the zone-aware model, so the data would already be complete in the database. In the future, though, I will go case by case and while redesigning the UIs to accommodate timezones - ZonedDateTime will be replacing DateTime in those widgets. While the ones that will keep operating in local timezone - will keep the zone oblivious version. Deciding the behavior, especially updates and filters between different zones in UI, and only exposing it all to the users that care will be a challenge on its own. Stay tuned for other posts if you are interested.

Drift

1. Previously I was using the recommended approach with store_date_time_values_as_text: true, so for me the change here is to switch back to store_date_time_values_as_text: false.
2. I also had to adjust .drift files not to use juliandate for date comparison:

   --- a/queries.drift
   +++ b/queries.drift
   @@ -35,7 +35,7 @@
        LEFT JOIN transactions ct ON ct.credit_operation_id = ao.id
        LEFT JOIN transactions dt ON dt.debit_operation_id = ao.id
        WHERE ao.account_id IN :accountIds
   -      AND julianday(COALESCE(ct.time, dt.time)) <= julianday(:atTime)
   +      AND COALESCE(ct.time, dt.time) <= :atTime
      )
      SELECT ot.account_id, ot.running_total FROM operation_totals ot
      WHERE row_num = 1;  -- Get only latest operation for each account

3. And we need a converter from the 2 columns into/from the ZonedDateTime model:

   ZonedDateTime zonedDateTimeFromColumns(DateTime instant, String tz) =>
       ZonedDateTime.fromInstant(instant: instant, tz: tz);
   
   ZonedDateTime? zonedDateTimeFromColumnsOrNull(
     DateTime? instant,
     String? tz,
   ) {
     if (instant == null && tz == null) {
       return null;
     }
     if (instant == null || tz == null) {
       throw StateError(
         'Corrupt time pair: instant=$instant, tz=$tz',
       );
     }
     return zonedDateTimeFromColumns(instant, tz);
   }

These are 2 simple functions. I will use them directly in the repository in my custom conversion logic rather than implementing a drift type converter because it is not 1 column to 1 model conversion.

Conclusion

If you care about precision in finance - you should know in what timezone assets are held and reported.

SQLite and Dart let you only handle UTC and 'local' timezones, but fall short when you need to reference a timezone that is neither of those.

One option is to use a TEXT representation of a date, but you lose cross timezone sorting in that case. If you want to preserve it - a second column with an IANA timezone id is the answer.

Achieving this requires a custom solution on all levels of the app from the database all the way to the UI.