Strings have a number of other disadvantages in this case. They take more computations to compare, they take time to parse when you read them, and they take longer to build when you insert them. Generally, storing dates as a number of some sort is ideal.
Building a query to return the value as a human readable string is fairly easy: SELECT datetime(2455133.71759947); -- returns '2009-10-29 05:13:20' I imagine the timezones aren't documented, because they aren't actually stored if the Julian format is used internally (they have to be converted to get the Julian in UTC.) If you use a string, it can store the timezone I guess, but it will cost you in terms of speed. John -----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Dan Bishop Sent: Thursday, October 29, 2009 12:26 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] Late data typing. Am I missing something? John Crenshaw wrote: > SQLite has plenty of date editing routines. Dates are stored in a double > as a Julian date. Well, that's one way of doing it. I store them as strings because I wanted a human-readable format. The downside is that this requires 19 bytes instead of 8. I wish SQLite could handle the storage optimization behind the scenes. > SQLite's understanding of "dates" is capable of > supporting null, date, time, or datetime. The only real problem is that > timezone is not stored, dates are always stored and retrieved in UTC, > and dates with timezones are converted prior to storage. Wow! I didn't realize that SQLite supported timezones, but sure enough, it does: sqlite> select datetime('2009-10-28T22:54:52-05:00'); 2009-10-29 03:54:52 Why isn't this documented at http://www.sqlite.org/lang_datefunc.html ? _______________________________________________ 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
