I assume you know about the timezone (tz) database maintained by the Internet Assigned Numbers Authority (IANA) -- it is sometimes referred as the Eggert/Olson database -- after its code and data maintainers.
http://www.iana.org/time-zones "The tz database is published as a set of text files <http://en.wikipedia.org/wiki/Text_file> which list the rules and zone transitions in a human-readable format. For use, these text files are compiled <http://en.wikipedia.org/wiki/Compiler> into a set of platform-independent binary files <http://en.wikipedia.org/wiki/Binary_file>—one per time zone. The reference source code includes such a compiler called *zic* (zone information compiler), as well as code to read those files and use them in standard APIs <http://en.wikipedia.org/wiki/Application_programming_interface> such as localtime() and mktime(). ...The Olson timezone IDs are also used by the Unicode Common Locale Data Repository <http://en.wikipedia.org/wiki/Common_Locale_Data_Repository> (CLDR) and International Components for Unicode <http://en.wikipedia.org/wiki/International_Components_for_Unicode> (ICU). For example, the CLDR Windows–Tzid table maps Microsoft Windows time zone IDs to the standard Olson names." http://en.wikipedia.org/wiki/Tz_database I assume the tz database could be imported into SQLite assuming that's not what they are using already. As long as time and date strings have leading zeros ("01") and not just ("1") it is easy to use SQLite string functions to pull them apart and use in calculations, but using this method you have to keep track of the modulus (2 AM - 5 hours is?) yourself. I learned about the "Olson database" from Wes McKinney's book "Python for Data Analysis." CAUTION: check the book's errata website http://oreil.ly/python_for_data_analysis before following any instructions in the book -- the field is changing very rapidly and in just over a year -- several key instructions (including which Python distribution to use) are already out of date. Wes has two sections on time zones: pages 18-26 where he downloads a usgov_bitly dataset and analyzes the time zone and pages 303-306 where he analyzes date stamps and mentions the ptz AND pandas Python libraries and the "Olson database" Jim Callahan Orlando, FL On Tue, Jul 29, 2014 at 9:41 AM, Will Fong <w...@digitaldev.com> wrote: > Hi, > > How are timezones best handled? Since dates are stored in GMT, when I > go to display them, I need to add/subtract the timezone. That's not > too hard when I can just store the timezone as "-5" for EST. When I'm > providing a date to query on, I would have to apply the reverse of the > timezone, "+5", to normalize it to GMT. > > That kinda sucks :( > > I come from PostgreSQL, so I normally set at the connection level the > timezone and PG handles all the conversions. Does SQLite have a > similar feature? > > Is there a standard way to handle this? > > Thanks, > -will > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
