On 23 Oct 2012, at 3:42am, John Gabriele <[email protected]> wrote: > Which column affinity is most customary to use for storing "YYYY-MM-DD > HH:MM:SS" datetime values?
Text. They are just text. As you've figured out, SQLite has no datetime datatype. > The docs at http://sqlite.org/datatype3.html , section 1.2, say "the > built-in Date And Time Functions of SQLite are capable of storing > dates and times as TEXT, REAL, or INTEGER values: ... INTEGER as Unix > Time", so, I'd expected "that_date" to be a large integer, for > example, like this: > > 1|2012-10-23 02:26:03|1350959558|2012-10-23 02:26:03 The function you used, datetime('now'), returns a text result. If you want that value converted into a number you have to make it happen either in your own code or by using SQLite functions, perhaps julianday('now') OR datetime('now', 'unixepoch'). See many examples in <http://www.sqlite.org/lang_datefunc.html> > Also, tangentially-related question: does each value in a row have its > own storage class? Is it a separate bit of data associated with (and > stored somewhere for) every single item? Is there a way I can ask > sqlite what's the storage class of a given element of data? Yes. Yes. And see typeof() in <http://www.sqlite.org/lang_corefunc.html> for instance SELECT typeof(datetime('now')) Simon. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
