I need the timezone of a timestamp in SQLite

Question

My app uses SQLite timestamps which are UTC. This has been fine for a long time.

But now I need to know the time zone in which a particular record was created.

I am happy to continue to store UTC, but I need to display data to the user using the originating zone.

This could easily be accomplished if SQLite supported a timestamp with timezone (+/-HHMM). It could also be easily accomplished if SQLite had some ability to extract the current timezone in '+/-HHMM' format.

Is there any way to get the 'current' time zone from SQLite? Or to get a timestamp that includes the timezone?

To be clear I like and want UTC. It is good, but I also need the originating TZ. I am happy to store this in another field if necessary. Also happy to store two date fields if necessary.

Edit1: The best I can come up with is:

select round(24*60*60*(julianday('now','localtime')-julianday('now')));

which does produce an offset in seconds, but is cumbersome and non-standard. I'd love a 'current_timestamp with timezone' analogue.

Edit2: At this point I am not sure why I'd bother storing the Julian offset rather than just a second date field representing local time. I guess it saves a few bytes, but that's about it.

It is necessary to store it separately, there really isn't any way to handle it otherwise. — Sami Kuhmonen
I'm happy to store separately, but is there a better way to get it in +/-HHMM format? — RabidMutant
Do understand that a time zone is not the same as a time zone offset. More on this in the timezone tag wiki. In particular, the current offset may not be the correct offset for the timestamp you are working with. Also, the "timestamp with time zone" types of other databases (mysql, postgres, etc) don't usually persist the offset (despite the name). datetimeoffset in SQL Server does though. — Matt Johnson-Pint
I'm wondering though - why do you need to calculate the offset within SQLite? Why not calculate it in the application layer and the store it into a different field? — Matt Johnson-Pint
You are quite right, I was imprecise. I want the prevailing offset so I can always get back to the local time at the time the record was written/updated. I'd like the database to do it since I offer the database to do as much as possible. This Wil most likely be done in default values and triggers. — RabidMutant

Matt Johnson-Pint Matt Johnson-Pint · Accepted Answer · 2016-07-17T19:14:51

The approach you showed in your answer is reasonable if you replace now with the actual timestamp in question. In other words, don't assume the current offset is the correct offset for all timestamps.

Do note though that this will only give you the time zone offset for the current machine. Since SQLite often runs in-process with an application, that's probably ok. But if for some reason your data originates elsewhere, there's no magic that can recall which time zone the data originated from.

I need the timezone of a timestamp in SQLite

1 Answers