Sorter version of a longer bit I was writing that got complicated: Expressions
don't have "Affinity" so when both sides of a comparison operator are
expressions they need to be the same type for it to mean anything. One of the
ways to do that in this example is to move the +300 inside the strftime call,
so that both sides end up as text...
sqlite> select strftime('%s', '2017-10-11 10:04:43', '+300 seconds') <
strftime('%s','2017-10-11 10:04:43');
strftime('%s', '2017-10-11 10:04:43', '+300 seconds') <
strftime('%s','2017-10-11 10:04:43')
0
Other options include using cast, or adding +0 to the other expression to turn
it into an integer as well.
sqlite> select strftime('%s', '2017-10-11 10:04:43') + 300 <
strftime('%s','2017-10-11 10:04:43') + 0;
strftime('%s', '2017-10-11 10:04:43') + 300 < strftime('%s','2017-10-11
10:04:43') + 0
0
-----Original Message-----
From: David Raymond
Sent: Wednesday, October 11, 2017 9:10 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: RE: [sqlite] Possible bug with strftime('%s') < strftime('%s')
See https://sqlite.org/draft/datatype3.html in section 4.1, Sort Order. Any
integer is less than any text. So you'll want to have both as one of the number
types to do valid comparison.
4.1 Sort Order
The results of a comparison depend on the storage classes of the operands,
according to the following rules:
A value with storage class NULL is considered less than any other value
(including another value with storage class NULL).
An INTEGER or REAL value is less than any TEXT or BLOB value. When an
INTEGER or REAL is compared to another INTEGER or REAL, a numerical comparison
is performed.
A TEXT value is less than a BLOB value. When two TEXT values are compared
an appropriate collating sequence is used to determine the result.
When two BLOB values are compared, the result is determined using memcmp().
sqlite> select typeof(strftime('%s', '2017-10-11 10:04:43') + 300);
typeof(strftime('%s', '2017-10-11 10:04:43') + 300)
integer
sqlite> select typeof(strftime('%s', '2017-10-11 10:04:43'));
typeof(strftime('%s', '2017-10-11 10:04:43'))
text
David Raymond | GIS Engineer | TomTom | Lebanon, NH, United States
e-mail: david.raym...@tomtom.com | office +1 603 306 8498 | www.tomtom.com
-----Original Message-----
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On
Behalf Of Eric Bollengier
Sent: Wednesday, October 11, 2017 8:55 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Possible bug with strftime('%s') < strftime('%s')
Hello,
I have noticed a problem in SQLite 3.20.1 for a simple operation based
on strftime('%s').
With SQLite 3.20.1 and 3.6.18
sqlite> select (strftime('%s', '2017-10-11 10:04:43') + 300) <
strftime('%s', '2017-10-11 10:04:43');
1
If I use the CAST operator on the second member, it works
sqlite> select
(strftime('%s', '2017-10-11 10:04:43') + 300) < CAST(strftime('%s',
'2017-10-11 10:04:43') as decimal);
0
If I use the following query, it works too:
sqlite> select
(strftime('%s', '2017-10-11 10:04:43') + 300 - strftime('%s',
'2017-10-11 10:04:43')) < 0;
0
on SQlite 2.8, the operator < with strftime('%s') works.
Any idea if it is the expected behavior?
Thanks,
Best Regards,
Eric
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
