On 3 Jan 2015, at 1:12am, J Decker <d3c...@gmail.com> wrote:
> On Fri, Jan 2, 2015 at 4:44 PM, Simon Slavin <slav...@bigfraud.org> wrote:
>
>> On 3 Jan 2015, at 12:12am, J Decker <d3c...@gmail.com> wrote:
>>
>>> https://www.sqlite.org/datatype3.html /* lists DateTime as a distinct
>>> type */
I'm going to answer this in detail because you still don't understand what's
happening and how to work with SQLite.
SQLite does not have 'column types', it has 'column affinity'. This means that
if you declare a column as NUMERIC but try to store a string in it SQLite will
store the string rather than generating an error message and/or storing NULL.
Remember this because it has an effect further down.
>> No it doesn't. It says that if you try to define a column as DATETIME
>> SQLite will understand it as you wanting a column with NUMERIC affinity.
>
> I know... but that first implies that it will work as a number, esp. if
> the content is a understood datetime format...
No, it means that if you save a number in that column it will be understood as
a number, and if you save a string in that column it will be understood as a
string. Neither of them will be understood as anything to do with dates or
times. SQLite ignores the fact that you originally specified DATETIME and acts
exactly the same as if you'd originally specified NUMERIC. It will store a
number or a string, and it will understand it as a number or a string, never
anything to do with dates or times.
> which I was quite happy
> with, decided that maybe local-prefixed self-descriptive offset times could
> be nice... (not sure how -3:15 is handled... is that -3 + 45?... but that's
> well out of scope)
'-3:15' is a string. It has a colon in it. Numbers don't have colons in them.
> okay I wrote invalid times. I thought the colon was optional in the
> timezone offset portion
All the values you supplied will be understood as strings. So given the
following values
2013-10-07 06:23:19.120
2013-10-07T01:23:19.120Z
2013-10-07 08:23:19.120-04:00
they will be understood to be in this order:
2013-10-07 06:23:19.120
2013-10-07 08:23:19.120-04:00
2013-10-07T01:23:19.120Z
because they're being understood as strings. ('6' < '8' and ' ' < 'T'). It
never even occurs to SQLite that they might be times or dates.
If you need your values understood as timedate you /must/ use the timedate
functions.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
