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
, where X
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?
SQLite does have support for converting between UTC and localtime, using the utc and localtime modifiers, however, so that could also be migrated into the query, if needed.
As noted SQLite (being “lite”) has no support for time zone, so you should adjust your date-time values into UTC for insertion into SQLite. For this reason and other reasons, programmers should learn to think in UTC while on the job.
First, create a new table named datetime_real . Second, insert the “current” date and time value into the datetime_real table. We used the julianday() function to convert the current date and time to the Julian Day. Third, query data from the datetime_real table.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With