Hi,
Which column affinity is most customary to use for storing "YYYY-MM-DD
HH:MM:SS" datetime values?
I tried this:
~~~sql
create table t1 (
id integer primary key,
this_date text,
that_date int,
other_date none);
insert into t1 (this_date, that_date, other_date)
values (datetime('now'), datetime('now'), datetime('now'));
select * from t1;
~~~
and the output is the same for all three columns:
1|2012-10-23 02:26:03|2012-10-23 02:26:03|2012-10-23 02:26:03
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
but it does not. It would seem that the value returned by
datetime('now') would be coerced to int since that's the affinity of
the column I'm putting it in...
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?
Thanks!
---John
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
