On Sat, Sep 4, 2010 at 7:31 AM, Mike Zang <mikez...@yahoo.co.jp> wrote: > I try to convert data to SQLite3 for iPad, please give me some detail > suggestion. > > I think that I can save date value as below to SQLite3, I want to know > which is better, or anything else if you have good idea. > > 1. integer as seconds since 1970 > 2. integer as days since 1970 > 3. string as '2010-09-03' > 4. string as '10-09-03'
sqlite has julianday and date sql functions which convert back and forth between julianday (number of days since noon in Greenwich on November 24, 4714 B.C.) and yyyy-mm-dd representations and also handle other manipulations in those formats so you probably want to choose one of those. yyyy-mm-dd does have the advantage that its easier to look at the raw data in the database. Also, if you are only dealing with dates and do not need to consider time zones then its best to use a representation that uses neither times nor time zones since those can introduce errors which are artifacts of the representation. time zone errors (confusion between UTC and current time zone) can be particularly subtle. sqlite> select date("now"); 2010-09-04 sqlite> select date("2000-01-01", "+1 day"); 2000-01-02 sqlite> select julianday(date("now")) - julianday("2010-09-01"); 3.0 sqlite> select date(julianday(date("now"))); 2010-09-04 See: http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users