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
