Slightly more efficient code is generated for the BETWEEN version (the LHS of the between is only calculated once). It is also somewhat easier to read.
sqlite> select x from x where x between 1 and 10; QUERY PLAN `--SCAN TABLE x addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 15 0 00 Start at 15 1 OpenRead 0 2 0 1 00 root=2 iDb=0; x 2 Noop 0 0 0 00 Begin WHERE-loop0: x 3 CursorHint 0 0 0 expr 00 4 Rewind 0 13 0 00 5 Column 0 0 1 00 r[1]=x.x 6 Lt 3 12 1 (BINARY) 51 if r[1]<r[3] goto 12 7 Gt 4 12 1 (BINARY) 51 if r[1]>r[4] goto 12 8 Noop 0 0 0 00 Begin WHERE-core 9 Column 0 0 5 00 r[5]=x.x 10 ResultRow 5 1 0 00 output=r[5] 11 Noop 0 0 0 00 End WHERE-core 12 Next 0 5 0 01 13 Noop 0 0 0 00 End WHERE-loop0: x 14 Halt 0 0 0 00 15 Transaction 0 0 1 0 01 usesStmtJournal=0 16 Integer 1 3 0 00 r[3]=1 17 Integer 10 4 0 00 r[4]=10 18 Goto 0 1 0 00 sqlite> select x from x where x >= 1 and x <= 10; QUERY PLAN `--SCAN TABLE x addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 16 0 00 Start at 16 1 OpenRead 0 2 0 1 00 root=2 iDb=0; x 2 Noop 0 0 0 00 Begin WHERE-loop0: x 3 CursorHint 0 0 0 AND(GE(c0,1),LE(c0,10)) 00 4 Rewind 0 14 0 00 5 Column 0 0 1 00 r[1]=x.x 6 Lt 2 13 1 (BINARY) 51 if r[1]<r[2] goto 13 7 Column 0 0 1 00 r[1]=x.x 8 Gt 3 13 1 (BINARY) 51 if r[1]>r[3] goto 13 9 Noop 0 0 0 00 Begin WHERE-core 10 Column 0 0 4 00 r[4]=x.x 11 ResultRow 4 1 0 00 output=r[4] 12 Noop 0 0 0 00 End WHERE-core 13 Next 0 5 0 01 14 Noop 0 0 0 00 End WHERE-loop0: x 15 Halt 0 0 0 00 16 Transaction 0 0 1 0 01 usesStmtJournal=0 17 Integer 1 2 0 00 r[2]=1 18 Integer 10 3 0 00 r[3]=10 19 Goto 0 1 0 00 --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Cecil Westerhof >Sent: Wednesday, 29 August, 2018 23:53 >To: SQLite mailing list >Subject: [sqlite] Better way to get range of dates > >When getting data between a range of dates you can use: > WHERE date >= DATE('now', '-7 days') > AND date < DATE('now') > >or: > WHERE date BETWEEN > DATE('now', '-7 days') > AND DATE('now', '-1 days') > >Is there a preferred way? In a way I like the second better. > >-- >Cecil Westerhof >_______________________________________________ >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
