Possible bug with strftime('%s') < strftime('%s')

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Possible bug with strftime('%s') < strftime('%s')

Eric Bollengier
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Possible bug with strftime('%s') < strftime('%s')

David Raymond
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: [hidden email]  | office +1 603 306 8498 | www.tomtom.com

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Eric Bollengier
Sent: Wednesday, October 11, 2017 8:55 AM
To: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Possible bug with strftime('%s') < strftime('%s')

David Raymond
In reply to this post by Eric Bollengier
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: [hidden email]
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: [hidden email]  | office +1 603 306 8498 | www.tomtom.com

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Eric Bollengier
Sent: Wednesday, October 11, 2017 8:55 AM
To: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Possible bug with strftime('%s') < strftime('%s')

Don V Nielsen
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 <[hidden email]>
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: [hidden email]
> 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: [hidden email]  | office +1 603 306 8498 |
> www.tomtom.com
>
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of Eric Bollengier
> Sent: Wednesday, October 11, 2017 8:55 AM
> To: [hidden email]
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Possible bug with strftime('%s') < strftime('%s')

R Smith

On 2017/10/11 7:15 PM, Don V Nielsen wrote:
> So strftime always returns TEXT. Correct?

Yes. The "str" in "strftime" means "string" which is text output. You
can read the name "strftime" as "string-formatted-time value".

> 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?

Yes. When you concatenate/add a string and integer together some SQL
engines will try to give a sensible result, so that '5' + 3 will yield 8
because 3 is INT and it reckons that '5' probably meant 5 since it is
added to another INT and the 5 doesn't have an explicit type. But, if
you force one of the terms to be string, such as CAST( 5 AS TEXT) or
strftime(%s,5) or use the value as a parameter to a function that
expects a specific type, then the engine might assume stuff, or try to
make sense of it, but in general when you start these shenanigans you
are on thin ice over "UNDEFINED" territory, which is what bit the OP
since the result in one version of SQlite differed from another version
(which the devs might adjust, but it's not a bug since mixing types is
not strictly supported).

You should never mix types when you expect a certain output. use CAST to
force the type you need, especially before arithmetic. Don't leave
correct interpretation up to the engine, even if it works mostly.

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Possible bug with strftime('%s') < strftime('%s')

Tim Streater-3
On 11 Oct 2017, at 18:53, R Smith <[hidden email]> wrote:

> Yes. When you concatenate/add a string and integer together some SQL
> engines will try to give a sensible result, so that '5' + 3 will yield 8
> because 3 is INT and it reckons that '5' probably meant 5 since it is
> added to another INT and the 5 doesn't have an explicit type. But, if
> you force one of the terms to be string, such as CAST( 5 AS TEXT) or
> strftime(%s,5) or use the value as a parameter to a function that
> expects a specific type, then the engine might assume stuff, or try to
> make sense of it, but in general when you start these shenanigans you
> are on thin ice over "UNDEFINED" territory, which is what bit the OP
> since the result in one version of SQlite differed from another version
> (which the devs might adjust, but it's not a bug since mixing types is
> not strictly supported).

All my times and dates are stored as seconds since the epoch (in a double for reasons that escape me at the minute). As a result I've never had a problem trying to compare them.

My philosophy is: internal format, seconds. External format (for display purposes), convert to the format the user wants.



--
Cheers  --  Tim
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Possible bug with strftime('%s') < strftime('%s')

David Raymond
In reply to this post by Don V Nielsen
All correct, yes.

Of course now I think about it, strftime always being a string means that you either should avoid the '%s' conversion or cast it to a number on both sides, so you don't get the '100' < '20' situation.

Better yet, either one of the datetime() or julianday() functions (with the same one used consistently in all places) will work best for comparison since the output for either one sorts correctly against itself. strftime() should be saved for display formatting.


sqlite> select strftime('%s', '1970-01-01 00:01:40') < strftime('%s', '1970-01-01 00:00:20');
1

sqlite> select datetime('1970-01-01 00:01:40') < datetime('1970-01-01 00:00:20');
0

sqlite> select julianday('1970-01-01 00:01:40') < julianday('1970-01-01 00:00:20');
0

sqlite> select datetime('now', '+300 seconds') < datetime('now');
0

sqlite> select datetime('now', '-300 seconds') < datetime('now');
1

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Don V Nielsen
Sent: Wednesday, October 11, 2017 1:15 PM
To: SQLite mailing list
Subject: Re: [sqlite] Possible bug with strftime('%s') < strftime('%s')

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?

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Possible bug with strftime('%s') < strftime('%s')

Don V Nielsen
> Of course now I think about it, strftime always being a string means that
> you either should avoid the '%s' conversion or cast it to a number on
both
> sides, so you don't get the '100' < '20' situation.

>> sqlite> select strftime('%s', '1970-01-01 00:01:40') < strftime('%s',
'1970-01-01 00:00:20');
>> 1

Ding Ding Ding. A winner. That was my fear is somehow numerics treated as
text was going to upset an apple cart somewhere.

Thanks for the followups and illustrations!
dvn

On Wed, Oct 11, 2017 at 1:11 PM, David Raymond <[hidden email]>
wrote:

> All correct, yes.
>
> Of course now I think about it, strftime always being a string means that
> you either should avoid the '%s' conversion or cast it to a number on both
> sides, so you don't get the '100' < '20' situation.
>
> Better yet, either one of the datetime() or julianday() functions (with
> the same one used consistently in all places) will work best for comparison
> since the output for either one sorts correctly against itself. strftime()
> should be saved for display formatting.
>
>
> sqlite> select strftime('%s', '1970-01-01 00:01:40') < strftime('%s',
> '1970-01-01 00:00:20');
> 1
>
> sqlite> select datetime('1970-01-01 00:01:40') < datetime('1970-01-01
> 00:00:20');
> 0
>
> sqlite> select julianday('1970-01-01 00:01:40') < julianday('1970-01-01
> 00:00:20');
> 0
>
> sqlite> select datetime('now', '+300 seconds') < datetime('now');
> 0
>
> sqlite> select datetime('now', '-300 seconds') < datetime('now');
> 1
>
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of Don V Nielsen
> Sent: Wednesday, October 11, 2017 1:15 PM
> To: SQLite mailing list
> Subject: Re: [sqlite] Possible bug with strftime('%s') < strftime('%s')
>
> 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?
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Possible bug with strftime('%s') < strftime('%s')

Keith Medcalf
In reply to this post by David Raymond

>Better yet, either one of the datetime() or julianday() functions
>(with the same one used consistently in all places) will work best
>for comparison since the output for either one sorts correctly
>against itself. strftime() should be saved for display formatting.

Only for a timestring with a constant offset from UT1.  The default is a fixed offset of 00:00, (Zulu, GMT, or UT1, whatever you want to call it).  Some people erroneously call this UTC but it is not.  UTC has a variable number of seconds in a day.  Zulu/GMT/UT1 have 86400 seconds in a day, never more and never less.  

If you store "localtime" or an "instant time" with an offset from GMT, then it cannot be sorted (unless all the offsets are the same, that is).

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users