Consider asql> select '1' >= 1, '1' <= 1; '1' >= 1 '1' <= 1 -------- -------- 1 0
This is because of the implicit ordering of TEXT and INTEGER values. Strftime() returns a text; in the expression "strftime() + 1" the arithmetic operator "casts" its left hand operand to integer (actually: applies numeric affinity) because SQLite is trying to be nice instead of complaining about a type mismatch and yields something numeric. The relational operator does not cast ist operands in that way. -----Urspr?ngliche Nachricht----- Von: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Rob Willett Gesendet: Donnerstag, 18. Februar 2016 13:16 An: SQLite mailing list Betreff: Re: [sqlite] Can't understand why I need this cast Quad, Thanks, that almost makes sense. What still confuses us is that the other maths expressions work OK without a cast. e.g. strftime('%s' , starttime) - (180 * 60) and (strftime('%s' , starttime) + (180 * 60)) - strftime('%s' , starttime) and strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 * 60)) , all work but strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180 * 60)) doesn?t. So why does strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 * 60)) work and strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180 * 60)) not work. Thanks, Rob On 18 Feb 2016, at 11:25, Quan Yong Zhai wrote: > It looks like strftime('%s',...) should return an integer and > strftime('%J', ...) should return a double value. But it always return > text value. > ________________________________ > ???: Rob Willett<mailto:rob.sqlite at robertwillett.com> > ????: ?2016/?2/?18 18:20 > ???: SQLite mailing > list<mailto:sqlite-users at mailinglists.sqlite.org> > ??: [sqlite] Can't understand why I need this cast > > Hi, > > I?m sure this is a really dumb question but I?m struggling to > understand why the following SQL is needed for what should be a > trivial SQL expression. > > I?ve minimised the example down to (hopefully) make it simpler. > > I have a table with an ISO date StartTime in it held as a string. > > ???? > sqlite> .schema test1 > CREATE TABLE "Test1" ( > "Id" INTEGER NOT NULL, > "StartTime" TEXT NOT NULL, > PRIMARY KEY("Id") > ); > > ???? > > I add in some test ISO date data that *looks* OK > > ???? > sqlite> select * from test1; > 1|2011-05-03T05:00:00Z > ???? > > I run the following to check that the ISO date is between a range of > seconds, i.e. its greater than three hours ago and less than three > hours in the future. I know this is contrived but this is the minimum > test case from a far larger query. > > select > strftime('%s' , starttime) , > strftime('%s' , starttime) - (180 * 60) , > strftime('%s' , starttime) + (180 * 60) , > strftime('%s' , starttime) - (strftime('%s' , starttime) - (180 * > 60)) > , > , > strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 > * 60)) > , > strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180 > * 60)) > from > test1; > > What I expect to see is > > 1304398800|1304388000|1304409600|10800|10800|1|0 > > 1. the ISO start time as secs - Correct 2. The ISO start time in secs > minus 10800 - Correct 3, The ISO start time in secs plus 10800 - > Correct 4. The value 10800 to check that the maths is correct - > Correct 5. The value 10800 to check that the maths is correct - > Correct 6. A check that the ISO value in secs is greater than the > current time minus 10800 - Correct 7. A check that the ISO value in > secs is less than the current time plus > 10800 secs - INCORRECT > > if I CAST the value of the last column > > select > strftime('%s' , starttime) , > strftime('%s' , starttime) - (180 * 60) , > strftime('%s' , starttime) + (180 * 60) , > strftime('%s' , starttime) - (strftime('%s' , starttime) - (180 * > 60)) > , > (strftime('%s' , starttime) + (180 * 60)) - strftime('%s' , > starttime) > , > strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 > * 60)) > , > strftime('%s' , starttime) <= cast((strftime('%s' , starttime) + > (180 * > 60)) as integer) > from > test1; > > I get the right answer > > 1304398800|1304388000|1304409600|10800|10800|1|1 > > I?m puzzled as to why this should be. Why would I need to cast an > integer to an integer. I??m sure there?s an obvious answer but I can?t > find it. > > Any suggestions please? > > Thanks, > > Rob > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: hick at scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
