sqlite> select '11' <= 11;
0
sqlite> select '11' <= cast(11 as integer);
1
sqlite> select '11' >= 11;
1
Certainly seems odd...
On 18 February 2016 at 12:20, Rob Willett <rob.sqlite at robertwillett.com>
wrote:
> Tim,
>
> We actually do store the timestamps as epoch seconds, but we wrote a quick
> piece of SQL to test something out and wanted to use the ISO date. Thats
> when the SQL failed and we couldn?t understand why. We hate it when we don?t
> understand why things don?t work the way we expect. Our OCD kicks in and
> annoys us :)
>
> We?ve investigated it further and it still makes no sense, though Quan Yong
> Zhai has helped. It appears that
>
> strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 * 60))
>
> works BUT
>
> strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180 * 60))
>
> does not work.
>
> The only difference is the boolean operand.
>
> We know how to solve the problem, we?re puzzled though as our understanding
> is clearly wrong.
>
> Thanks for replying,
>
> Rob
>
> On 18 Feb 2016, at 12:14, Tim Streater wrote:
>
>> On 18 Feb 2016 at 10:20, Rob Willett <rob.sqlite at robertwillett.com> wrote:
>>
>>> I?m sure this is a really dumb question but I?m struggling to
>>> understand why the following SQL is needed for what should be a trivial
>>> SQL expression.
>>>
>>> I?ve minimised the example down to (hopefully) make it simpler.
>>>
>>> I have a table with an ISO date StartTime in it held as a string.
>>
>>
>> I'd be inclined to store your dates as seconds since the epoch. That way
>> arithmetic and comparisons become easy, and your SQL looks simpler. Convert
>> to a string for display. But perhaps your application prevents that for some
>> reason.
>>
>> --
>> Cheers -- Tim
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
