John Gabriele <[email protected]> wrote:
> Which column affinity is most customary to use for storing "YYYY-MM-DD
> HH:MM:SS" datetime values?
Text.
> 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
You inserted text values in all three columns. Run this statement, see for
yourself:
select typeof(this_date), typeof(that_date), typeof(other_date);
> 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
Yes. REAL for julian dates, INTEGER for seconds since Unix epoch, TEXT for
strings of the form you are asking about.
> so, I'd expected "that_date" to be a large integer
Why? datetime() returns a string. If you want an integer, make it
strftime('%s', 'now')
You seem to believe that SQLite date/time functions return some special data
type that can be automatically converted to either text or integer. That is not
the case - there is no dedicated type for date/time values.
> It would seem that the value returned by
> datetime('now') would be coerced to int
A string like '2012-10-23 02:26:03' doesn't resemble a valid textual
representation of any integer.
> Also, tangentially-related question: does each value in a row have its
> own storage class?
Generally, yes.
> Is it a separate bit of data associated with (and stored somewhere for) every
> single item?
Yes. SQLite file format is documented here: http://sqlite.org/fileformat2.html
> Is there a way I can ask
> sqlite what's the storage class of a given element of data?
typeof, sqlite3_column_type
--
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
