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

[Wout Mertens]

I feel safer now :)

On Fri, Oct 13, 2017, 12:57 PM Rowan Worth  wrote:

> In that case you would be well advised to use a monotonic clock source,
> rather than a "date-generating" clock. In linux this is the difference
> between providing CLOCK_MONOTONIC or CLOCK_REALTIME as the first argument
> to clock_gettime().
>
> But any API you might use to set a trigger for 2 seconds into the future is
> probably already based on a monotonic clock. Polling a realtime clock would
> make for a pretty convoluted implementation!
>
> -Rowan
>
> On 13 October 2017 at 18:42, Wout Mertens  wrote:
>
> > Thank you, very interesting!
> >
> > The leap second behavior is slightly worrying, basically anything
> > time-based (animations etc) will take a second longer? What if you want
> an
> > engine burn to last 2 seconds, set a trigger for 2 seconds from now, and
> > then it's burning 50% longer?
> >
> > On Thu, Oct 12, 2017, 3:50 PM Stephan Buchert 
> > wrote:
> >
> > > This has not much to do with the original question, but as physicist I
> > > cannot resist:
> > >
> > > National institutes (NIST in the US, NPL in the UK, PTB in Germany, to
> > name
> > > just a few) provide reference times in UTC, which are distributed
> > nowadays
> > > also via the internet, e.g. the NTP protocol. Therefore clocks of
> > > computers, smart phone etc. are, if at all, synchronized more or less
> > > successfully to UTC, and the timestamps that a software like Sqlite
> > handles
> > > are in the vast majority UTC, possibly plus a timezone offset. For
> > example,
> > > the message by Keith Medcalf has been stamped  *Wed Oct 11 21:53:05 UTC
> > > 2017.*
> > >
> > > What can go wrong?:
> > >
> > > 1) The local clock is synchronized now and then, in some cases as a
> > > consequence of the well-know leap seconds, by setting it abruptly to a
> > new
> > > time. Obviously this does not guarantee that the timestamps become
> > ordered
> > > the same as the events really happened. In the worst case a timestamp
> has
> > > the wrong day or even the wrong year (with potentially legal
> > consequences,
> > > e.g. for financial transactions).
> > >
> > > When is it right?:
> > >
> > > 2) Using the information from NTP, only the clock speed is adjusted to
> > > compensate for drifts. Leap seconds are announced in advance via NTP.
> But
> > > none of the major operating systems, Windows, Linux, Unix can
> internally
> > > represent times within leap seconds. Therefore the system clock is
> halted
> > > for the leap second. Calls for the system time within a leap second
> > return
> > > time stamps just before the leap second, having a small difference
> > between
> > > them such that their order is correct.
> > >
> > > Sqlite and applications are here at the mercy of the underlying system,
> > no
> > > matter how the time at the Sqlite level is presented, as floating point
> > > Julian day numbers, (milli- or micro) second counters from a certain
> > point
> > > in time (epoch) or so. Normally leap seconds don't need to be
> > > representable, as Sqlite/applications are not going to get exposed to
> > such
> > > time stamps (all the OSs cannot). But any timestamps are almost
> certainly
> > > (supposed to be) UTC, plus timezone offset.
> > >
> > > Finally UT1:
> > >
> > > 1) Unless you need to do stuff like tracking satellites, planets, stars
> > and
> > > other celestial objects with high precision from the Earth, you don't
> > need
> > > to know what it is.
> > >
> > > 2) UT1 is published by the https://www.iers.org/ as a daily correction
> > in
> > > SI seconds to UTC, distinguishing between predicted and final
> correction.
> > > As such UT1 does not have seconds, days etc. You can of course define a
> > UT1
> > > day as between the times when the corrected UTC passes midnight, and
> then
> > > divide this "day" into 86400 "seconds". From the physical viewpoint
> this
> > > would be a bit weird because these seconds then have a different length
> > > than the standard SI second, and their length also varies from day to
> > day.
> > > ___
> > > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Rowan Worth]

In that case you would be well advised to use a monotonic clock source,
rather than a "date-generating" clock. In linux this is the difference
between providing CLOCK_MONOTONIC or CLOCK_REALTIME as the first argument
to clock_gettime().

But any API you might use to set a trigger for 2 seconds into the future is
probably already based on a monotonic clock. Polling a realtime clock would
make for a pretty convoluted implementation!

-Rowan

On 13 October 2017 at 18:42, Wout Mertens  wrote:

> Thank you, very interesting!
>
> The leap second behavior is slightly worrying, basically anything
> time-based (animations etc) will take a second longer? What if you want an
> engine burn to last 2 seconds, set a trigger for 2 seconds from now, and
> then it's burning 50% longer?
>
> On Thu, Oct 12, 2017, 3:50 PM Stephan Buchert 
> wrote:
>
> > This has not much to do with the original question, but as physicist I
> > cannot resist:
> >
> > National institutes (NIST in the US, NPL in the UK, PTB in Germany, to
> name
> > just a few) provide reference times in UTC, which are distributed
> nowadays
> > also via the internet, e.g. the NTP protocol. Therefore clocks of
> > computers, smart phone etc. are, if at all, synchronized more or less
> > successfully to UTC, and the timestamps that a software like Sqlite
> handles
> > are in the vast majority UTC, possibly plus a timezone offset. For
> example,
> > the message by Keith Medcalf has been stamped  *Wed Oct 11 21:53:05 UTC
> > 2017.*
> >
> > What can go wrong?:
> >
> > 1) The local clock is synchronized now and then, in some cases as a
> > consequence of the well-know leap seconds, by setting it abruptly to a
> new
> > time. Obviously this does not guarantee that the timestamps become
> ordered
> > the same as the events really happened. In the worst case a timestamp has
> > the wrong day or even the wrong year (with potentially legal
> consequences,
> > e.g. for financial transactions).
> >
> > When is it right?:
> >
> > 2) Using the information from NTP, only the clock speed is adjusted to
> > compensate for drifts. Leap seconds are announced in advance via NTP. But
> > none of the major operating systems, Windows, Linux, Unix can internally
> > represent times within leap seconds. Therefore the system clock is halted
> > for the leap second. Calls for the system time within a leap second
> return
> > time stamps just before the leap second, having a small difference
> between
> > them such that their order is correct.
> >
> > Sqlite and applications are here at the mercy of the underlying system,
> no
> > matter how the time at the Sqlite level is presented, as floating point
> > Julian day numbers, (milli- or micro) second counters from a certain
> point
> > in time (epoch) or so. Normally leap seconds don't need to be
> > representable, as Sqlite/applications are not going to get exposed to
> such
> > time stamps (all the OSs cannot). But any timestamps are almost certainly
> > (supposed to be) UTC, plus timezone offset.
> >
> > Finally UT1:
> >
> > 1) Unless you need to do stuff like tracking satellites, planets, stars
> and
> > other celestial objects with high precision from the Earth, you don't
> need
> > to know what it is.
> >
> > 2) UT1 is published by the https://www.iers.org/ as a daily correction
> in
> > SI seconds to UTC, distinguishing between predicted and final correction.
> > As such UT1 does not have seconds, days etc. You can of course define a
> UT1
> > day as between the times when the corrected UTC passes midnight, and then
> > divide this "day" into 86400 "seconds". From the physical viewpoint this
> > would be a bit weird because these seconds then have a different length
> > than the standard SI second, and their length also varies from day to
> day.
> > ___
> > 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

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

[R Smith]

On 2017/10/13 12:42 PM, Wout Mertens wrote:

Thank you, very interesting!

The leap second behavior is slightly worrying, basically anything
time-based (animations etc) will take a second longer? What if you want an
engine burn to last 2 seconds, set a trigger for 2 seconds from now, and
then it's burning 50% longer?


I sincerely hope you are making a bot for Kerbal space program and not 
actually working for NASA :)


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Wout Mertens]

Thank you, very interesting!

The leap second behavior is slightly worrying, basically anything
time-based (animations etc) will take a second longer? What if you want an
engine burn to last 2 seconds, set a trigger for 2 seconds from now, and
then it's burning 50% longer?

On Thu, Oct 12, 2017, 3:50 PM Stephan Buchert  wrote:

> This has not much to do with the original question, but as physicist I
> cannot resist:
>
> National institutes (NIST in the US, NPL in the UK, PTB in Germany, to name
> just a few) provide reference times in UTC, which are distributed nowadays
> also via the internet, e.g. the NTP protocol. Therefore clocks of
> computers, smart phone etc. are, if at all, synchronized more or less
> successfully to UTC, and the timestamps that a software like Sqlite handles
> are in the vast majority UTC, possibly plus a timezone offset. For example,
> the message by Keith Medcalf has been stamped  *Wed Oct 11 21:53:05 UTC
> 2017.*
>
> What can go wrong?:
>
> 1) The local clock is synchronized now and then, in some cases as a
> consequence of the well-know leap seconds, by setting it abruptly to a new
> time. Obviously this does not guarantee that the timestamps become ordered
> the same as the events really happened. In the worst case a timestamp has
> the wrong day or even the wrong year (with potentially legal consequences,
> e.g. for financial transactions).
>
> When is it right?:
>
> 2) Using the information from NTP, only the clock speed is adjusted to
> compensate for drifts. Leap seconds are announced in advance via NTP. But
> none of the major operating systems, Windows, Linux, Unix can internally
> represent times within leap seconds. Therefore the system clock is halted
> for the leap second. Calls for the system time within a leap second return
> time stamps just before the leap second, having a small difference between
> them such that their order is correct.
>
> Sqlite and applications are here at the mercy of the underlying system, no
> matter how the time at the Sqlite level is presented, as floating point
> Julian day numbers, (milli- or micro) second counters from a certain point
> in time (epoch) or so. Normally leap seconds don't need to be
> representable, as Sqlite/applications are not going to get exposed to such
> time stamps (all the OSs cannot). But any timestamps are almost certainly
> (supposed to be) UTC, plus timezone offset.
>
> Finally UT1:
>
> 1) Unless you need to do stuff like tracking satellites, planets, stars and
> other celestial objects with high precision from the Earth, you don't need
> to know what it is.
>
> 2) UT1 is published by the https://www.iers.org/ as a daily correction in
> SI seconds to UTC, distinguishing between predicted and final correction.
> As such UT1 does not have seconds, days etc. You can of course define a UT1
> day as between the times when the corrected UTC passes midnight, and then
> divide this "day" into 86400 "seconds". From the physical viewpoint this
> would be a bit weird because these seconds then have a different length
> than the standard SI second, and their length also varies from day to day.
> ___
> 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] Possible bug with strftime('%s') < strftime('%s')

[Stephan Buchert]

This has not much to do with the original question, but as physicist I
cannot resist:

National institutes (NIST in the US, NPL in the UK, PTB in Germany, to name
just a few) provide reference times in UTC, which are distributed nowadays
also via the internet, e.g. the NTP protocol. Therefore clocks of
computers, smart phone etc. are, if at all, synchronized more or less
successfully to UTC, and the timestamps that a software like Sqlite handles
are in the vast majority UTC, possibly plus a timezone offset. For example,
the message by Keith Medcalf has been stamped  *Wed Oct 11 21:53:05 UTC
2017.*

What can go wrong?:

1) The local clock is synchronized now and then, in some cases as a
consequence of the well-know leap seconds, by setting it abruptly to a new
time. Obviously this does not guarantee that the timestamps become ordered
the same as the events really happened. In the worst case a timestamp has
the wrong day or even the wrong year (with potentially legal consequences,
e.g. for financial transactions).

When is it right?:

2) Using the information from NTP, only the clock speed is adjusted to
compensate for drifts. Leap seconds are announced in advance via NTP. But
none of the major operating systems, Windows, Linux, Unix can internally
represent times within leap seconds. Therefore the system clock is halted
for the leap second. Calls for the system time within a leap second return
time stamps just before the leap second, having a small difference between
them such that their order is correct.

Sqlite and applications are here at the mercy of the underlying system, no
matter how the time at the Sqlite level is presented, as floating point
Julian day numbers, (milli- or micro) second counters from a certain point
in time (epoch) or so. Normally leap seconds don't need to be
representable, as Sqlite/applications are not going to get exposed to such
time stamps (all the OSs cannot). But any timestamps are almost certainly
(supposed to be) UTC, plus timezone offset.

Finally UT1:

1) Unless you need to do stuff like tracking satellites, planets, stars and
other celestial objects with high precision from the Earth, you don't need
to know what it is.

2) UT1 is published by the https://www.iers.org/ as a daily correction in
SI seconds to UTC, distinguishing between predicted and final correction.
As such UT1 does not have seconds, days etc. You can of course define a UT1
day as between the times when the corrected UTC passes midnight, and then
divide this "day" into 86400 "seconds". From the physical viewpoint this
would be a bit weird because these seconds then have a different length
than the standard SI second, and their length also varies from day to day.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Keith Medcalf]

>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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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 <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

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

[David Raymond]

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

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

[Tim Streater]

On 11 Oct 2017, at 18:53, R Smith  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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 <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

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

[David Raymond]

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

Re: [sqlite] 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: 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] 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users