Re: [sqlite] Possible bug with strftime('%s') < strftime('%s')
>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')
> 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 Raymondwrote: > 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')
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')
On 11 Oct 2017, at 18:53, R Smithwrote: > 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] SQLite version 3.21.0 in approximately two weeks
> On Oct 11, 2017, at 4:26 AM, Richard Hippwrote: > > A summary of changes for the 3.21.0 release can be seen at > >https://sqlite.org/draft/releaselog/3_21_0.html Item #5: "A forger can subverted" ==> "A forger can subvert" ___ 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')
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')
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 Raymondwrote: > 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')
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] sqlite3_expert Status
On 11 Oct 2017, at 10:53am, Dominique Deviennewrote: > For those like me who missed / forgot / didn't know about SQLite Expert, > read about it here: https://www.sqlite.org/cgi/src/tree?name=ext/expert --DD That’s clever. I like that. Simon. ___ 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')
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')
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
Re: [sqlite] Script Embedded SQLite With TCL
There doesn’t seem to be a straightforward way to get the SQLite handle from the Tcl SQL command, but it is possible. https://github.com/flightaware/Pgtcl/blob/master/generic/pgtclSqlite.c#L838 On 10/7/17, 9:31 AM, "sqlite-users on behalf of apajabo yaro"wrote: Hello,I am writing a C++ application that embeds TCL and for its database operations I am also embedding SQLite in it.I would like to be able to do the following:1. Write TCL scripts for the embedded SQLite from the embedded TCL interpeter.2. Pass an SQLite connection from the embedded TCL interpreter to C++ and use it in C++ for db operations, as well as the other way around. I would appreciate your kind suggestions.Yaro. ___ 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] SQLite version 3.21.0 in approximately two weeks
The planned release date for SQLite version 3.21.0 is 2017-10-25. The latest release candidate is available as the "Prerelease Snapshot" at https://sqlite.org/download.html or can be accessed directly from the version control system at https://sqlite.org/src/timeline?c=branch-3.21 A summary of changes for the 3.21.0 release can be seen at https://sqlite.org/draft/releaselog/3_21_0.html Draft documentation for the 3.21.0 release is available at: https://www.sqlite.org/draft/ A release checklist can been seen at https://www.sqlite.org/checklists/321/index The release is *planned* for 2017-10-25 but the actually release will occur whenever the checklist goes all-green. Your testing, analysis, and feedback prior to this release is greatly appreciated! -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite3_expert Status
On Wed, Oct 11, 2017 at 10:58 AM, Philip Bennefallwrote: > I was curious to know the status of the sqlite3_expert extension? I > followed its development with great interest earlier in the year and was > wondering if there are any plans to merge it to trunk? What kind of work > remains (if any) before it can be considered complete? > For those like me who missed / forgot / didn't know about SQLite Expert, read about it here: https://www.sqlite.org/cgi/src/tree?name=ext/expert --DD PS: part of "schemalint" apparently: https://www.sqlite.org/cgi/src/timeline?t=schemalint=200 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] sqlite3_expert Status
Dear SqLite developers, I was curious to know the status of the sqlite3_expert extension? I followed its development with great interest earlier in the year and was wondering if there are any plans to merge it to trunk? What kind of work remains (if any) before it can be considered complete? Thanks, Philip Bennefall ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users