On Mon, Oct 22, 2012 at 10:42 PM, John Gabriele <[email protected]> wrote:
> Hi,
>
> Which column affinity is most customary to use for storing "YYYY-MM-DD
> HH:MM:SS" datetime values?
>
I always use DATE or DATETIME or TIME, depending on what I'm storing. I
believe these always have affinity of NONE.
>
> 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...
>
You are way over-thinking his.
Conversions between numeric and text only occur if the result "looks" the
same when printing. so '12345' (text) will convert to 12345 (integer) and
back again. Affinity never changes the "look" of a value.
So never would affinity cause '2012-10-23 02:26:03' to be converted into
1350959163 because those two things do not look the same. If you need to
convert from ISO-8601 into unix-time, then do:
strftime('%s', '2012-10-03 02:26:03');
And if you need to convert from unix-time to ISO-8601, using this:
datetime(1350959163, 'unixepoch');
The following should always be a no-op:
datetime(strftime('%s', dateHere), 'unixepoch')
>
> 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
>
--
D. Richard Hipp
[email protected]
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
