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:

A bank account is in Europe, so the transaction is displayed in UTC+1

You are somewhere in Australia, so your local time is UTC+10

You write your salary with a literal time from your bank app, and the app treats it as your local time

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:

Associate a date-time value of a single row with a timezone value

Sort by datetime column correctly in absolute terms. All transactions happened in some order in reality, the order should stay true in my app.

Filter by ranges with second precision across timezones. e.g. an 'all transaction' list filtered by dates

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 DateTime s 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.

DartDateTime 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…