On 3 Jan 2015, at 12:12am, J Decker <[email protected]> wrote:
> https://www.sqlite.org/datatype3.html /* lists DateTime as a distinct type
> */
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.
> Since on the datefunc page
>
> Formats 2 through 10 may be optionally followed by a timezone indicator of
> the form "*[+-]HH:MM*" or just "*Z*". The date and time functions use UTC
> or "zulu" time internally, and so the "Z" suffix is a no-op. Any non-zero
> "HH:MM" suffix is subtracted from the indicated date and time in order to
> compute zulu time. For example, all of the following time strings are
> equivalent:
>
> 2013-10-07 08:23:19.120
> 2013-10-07T08:23:19.120Z
> 2013-10-07 04:23:19.120-04:00
> 2456572.84952685
>
> Defines equivalency... I would have assumed that inequalities could also be
> done.
Yep. That's equivalency for use in datetime functions. Not for datetime
values since SQLite does not understand datetime values. To process those you
need to convert your datetime into a string or a number.
> And since 'formats supported' are specified, one would assume that datetime
> columns
There are no datetime columns. Because SQLite doesn't have datetime as a
datatype.
> with supported strings would work with at least =, >= <=, <, >, !=
To do what you want use the datetime function "strftime('%s',value)" to convert
your stored time values into seconds since 1970. This will let you use the
operators you have listed above. Here's an example:
strftime('%s',receivedate) < strftime('%s',receivedate2)
This use will recognise all the string formats you've listed above.
Better still store your datetime values as numbers of seconds to start with
instead of inconsistent text string. Then you can use those columns in indexes
and do sorting and searching without having to use datetime functions.
Simon.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
