How to insert datetime with timezone to SQLite?
15,587
SQLite's built-in date and time functions understand the timezone specification in these strings, but with different timezones, any other operations on these strings (even searches and comparisons) will not work correctly.
If you want to handle time zones, you have to either
- convert all timestamps to one specific time zone (UTC), so that you can use them for sorting and searching; or
- leave the time zone information in there, but do all searching, sorting and other computations not in SQL but in your application.
Related videos on Youtube
Author by
Wei Shi
@Test public void me(){ assertEquals(me,PEOPLE.Programmer); }
Updated on September 16, 2022Comments
-
Wei Shi over 1 year
I know that to insert a datetime this format
yyyy-mm-dd hh:mm:ss
should be used.However my dataset has a timestamp field that looks like
yyyy-mm-dd hh:mm:ss +/-0X:00
, whereX
can take many values and is different from my computer's local timezone.What is the best way to insert a datetime field with such timezone information to SQLite?
-
farfareast over 11 yearsI would always insert the time converted to Greenwich (UTS+0) and clients would convert it to local time when retrieve from the database. In this way you do not need to store timezone in the database. If you really need to store timezone of the client that measured the time, put it into another column.
-
Clockwork-Muse over 11 years@EmmanuelN - That's a terrible idea - it's going to make working with date/times more difficult; always store values in the best type possible. farfareast has the better idea; although, I'd probably say UTC, which doesn't have daylight savings (unsure about Greenwich).
-
-
courtsimas over 5 yearsI believe this is no longer the case.
-
Derek Morrison over 2 yearsThanks for the info, CL. I'm curious about "any other operations on these strings". I had a bug where values stored in a
datetime
column with a time zone at the end (in the form "+/- 0X:00") where getting sorted with some other values mixed in the table without time zone info (so, UTC), and SQLite didn't sort correctly. Do you have a link to info about these unsupported operations? -
Wirsing over 2 years@DerekMorrison Those values are strings, and all operations except the built-in date/time functions are string operations.