Richard,
The example is highly contrived. The actual SQL we are/were actually
interested in is
strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 * 60)),
AND
strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180 * 60))
The rest of the SQL was to test what was happening with strftime and
whether integers being produced from the expressions to test the two SQL
statements above. We thought the contrived test expressions worked OK
which was why we were puzzled as to why
strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 * 60)),
worked and
strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180 * 60))
failed, though the only difference was the change in the boolean
operand.
As Tim Streater pointed out we would actually use epoch seconds as this
is more natural and we have that information in another database.
However we threw/hacked the above SQL together to test an idea we had,
specifically to find traffic incidents three hours before and three
hours after other traffic incidents.The test database table we tested it
with didn?t have the epoch seconds in, so we used strftime instead to
check our idea. Thats when we got results that puzzled us, we know we
have alternate and better solutions to actually doing the work, whats
worrying us is that our understanding of SQLite, strftime and implicit
cast conversions is incorrect. The fact that strftime returns a text
string is fine and that fits in with what we expected. We simple got the
rest of the conversions wrong. We appear to have created a boolean
expression that worked (a false positive) and that threw us back as we
misunderstood (quite badly) what was going on.
We now know we have to be more careful about how we use strftime and we
will be in our logic.
Thanks to everybody for the help, we?ve learnt something new today
which is always good.
Best wishes,
Rob
On 18 Feb 2016, at 12:45, Richard Hipp wrote:
> On 2/18/16, Rob Willett <rob.sqlite at robertwillett.com> wrote:
>>
>> select
>> strftime('%s' , starttime),
>> strftime('%s' , starttime) - (180 * 60),
>> strftime('%s' , starttime) + (180 * 60),
>> strftime('%s' , starttime) - (strftime('%s' , starttime) - (180 *
>> 60)),
>> (strftime('%s' , starttime) + (180 * 60)) - strftime('%s' ,
>> starttime),
>> strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 *
>> 60)),
>> strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180 *
>> 60))
>> from
>> test1;
>>
>
> I don't exactly understand why you are doing the query above, but for
> whatever your purpose is, the following formulation seems simpler to
> me:
>
> SELECT
> strftime('%s', starttime),
> strftime('%s', starttime, '-180 seconds'),
> strftime('%s', starttime, '+180 seconds'),
> (julianday(starttime) - julianday(starttime','-180 seconds'))*86400,
> (julianday(starttime, '+180 seconds') - julianday(starttime))*86400,
> julianday(starttime) >= julianday(starttime, '-180 seconds'),
> julianday(starttime) <= julianday(starttime, '+180 seconds')
> FROM
> test1;
>
>
>
>
> --
> D. Richard Hipp
> drh at sqlite.org
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
