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
