On 28 Sep 2011, at 3:52pm, Petite Abeille wrote: > On Sep 28, 2011, at 4:48 PM, Puneet Kishor wrote: > >> But, if I understood [http://www.sqlite.org/datatype3.html] correctly, there >> really is no such thing as DATETIME value. Internally, it is stored as TEXT >> anyway. > > Or as a number. Your choice: > > • TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS"). > • REAL as Julian day numbers, the number of days since noon in > Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian > calendar. > • INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 > UTC. > > Considering the amount of data you have, perhaps something like a unix time > would be more, hmmm, more frugal.
Of course, if he is consistent for any of these he can just replace > WHERE Datetime(u.downloaded_on) >= Datetime(p.project_start) with WHERE u.downloaded_on >= p.project_start and make indexes which include the columns. No conversion needed. The only reason to need conversion is if the source data is in one format in some rows and another format in other rows. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
