Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to insert datetime with timezone to SQLite?

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?

like image 468
Wei Shi Avatar asked Nov 09 '12 22:11

Wei Shi


People also ask

Does SQLite support timezone?

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.

How do I change timezone in SQLite?

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.

How do I insert the current date and time in SQLite?

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.


1 Answers

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.
like image 145
CL. Avatar answered Sep 28 '22 06:09

CL.