>>> *if* you know that the number *is* a date. >>> >> >> If the column has a type of timestamp, it should be safe to always >> assume that it IS a date. >sqlite> CREATE TABLE t1 (StartTime TIMESTAMP, Duration REAL); >sqlite> CREATE TABLE t2 AS SELECT StartTime, StartTime + Duration / >86400.0 AS StopTime FROM t1; >sqlite> SELECT sql FROM sqlite_master WHERE name = 't2'; >CREATE TABLE t2(StartTime TIMESTAMP,StopTime) > >What column type?
Still a timestamp (assuming that you stored the dates as Julian values and that the math was therefore valid.) SQLite doesn't care what the column type is listed as so it doesn't matter what sqlite_master says. All that matters is how you used the data, and in this case the calculated value is still a timestamp. >> Don't put non-dates in it, and there isn't a >> problem. I can't imagine a case where you would not know whether the >> value is a date. Am I missing something here? >> >Consider the case of an application using an SQLite database to store >its settings (like the Windows registry, but portable). The dynamic >type system is great for this. > >CREATE TABLE Settings (Name TEXT PRIMARY KEY, Value BLOB); > >Name Value >----------------- --------------- >LogPath 'E:\log\FooApp' >MaxAttachmentSize 2500000 >LastUpdate 2455130.1125 > >Now, in the SQLite equivalent of regedit, how is it supposed to know >that LastUpdate is timestamp 2009-10-25 14:42:00 but MaxAttachmentSize >is NOT the date 2132-08-31 12:00:00? Without knowledge of the >application that created this table, it can't. A system like this would need a type column as well. Storing dates as text doesn't change that, because at some level you'll still need to distinguish between regular text, and a date stored as text. Once you add a type column, it is no longer ambiguous. Dates don't have a special data type internally so you have to choose to treat them as dates for them to be dates. You can store them as strings and accept the performance hit that will come with that, or you can store them as Julian dates, and accept a little extra typing when you look at your data in a generic viewer. Either way though, it's only a date if you treat it like one. John _______________________________________________ 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
