> 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 <david.raym...@tomtom.com>
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:sqlite-users-boun...@mailinglists.sqlite.org]
> 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
> 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
