So strftime always returns TEXT. Correct? It was the application of +300 to
that result that changed the type to INTEGER. And had "+300 seconds" been
applied as a modifier in the strftime function, then the addition would
have occurred before producing the result, with the result being type TEXT.
Correct?
On Wed, Oct 11, 2017 at 9:56 AM, David Raymond <david.raym...@tomtom.com>
wrote:
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
