Hey Chris, thanks for your post.
I don’t think that a need for knowing the original timezone is that common — what apps usually do is they take a timestamp from database and they convert it to the human readable format based on the current system’s timezone.
What I find way more common though is actual sorting or comparing dates (i.e. you want to grab records that are not older than 24 hours).
And for those kind of queries storing raw timestamps seems to be more efficient, which is pretty important in case your table is big.
sqlite> create table a (date TEXT);
sqlite> create table b (date INTEGER);
sqlite> create index date_b on b (date);
sqlite> create index date_a on a (date); # can't really do expression-index as SQLite claims datetime() isn't deterministic
# some dummy inserts
sqlite> explain query plan select date from b where date > 3;
0|0|0|SEARCH TABLE b USING COVERING INDEX date_b (date>?)
sqlite> explain query plan select date from a where datetime(date) > datetime('2017-10-17T07:23:19.120+00:00');
0|0|0|SCAN TABLE a
The latter query needs to perform a full table scan as it needs to evaluate datetime on every single row in that table.
While this is hardly noticeable for smaller tables, I imagine it may be a serious performance hit for larger tables.
If your usecase indeed requires having information about the original timezone, I would go for having a separate column just to keep the timezone offset. That way you probably win all the benefits from both solutions with a relatively small sacrifice?
Let me know what you think!