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 p1p2p3p4 p5 comment
- - -- -
0 Init 0 15000 Start at 15
1 OpenRead 0 2 0 1 00 root=2 iDb=0; x
2 Noop 0 0 000 Begin WHERE-loop0: x
3 CursorHint 0 0 0 expr 00
4 Rewind 0 13000
5 Column 0 0 100 r[1]=x.x
6 Lt 3 121 (BINARY) 51 if r[1]r[4] goto 12
8 Noop 0 0 000 Begin WHERE-core
9 Column 0 0 500 r[5]=x.x
10 ResultRow 5 1 000 output=r[5]
11 Noop 0 0 000 End WHERE-core
12Next 0 5 001
13Noop 0 0 000 End WHERE-loop0: x
14Halt 0 0 000
15Transaction0 0 1 0 01 usesStmtJournal=0
16Integer1 3 000 r[3]=1
17Integer104 000 r[4]=10
18Goto 0 1 000
sqlite> select x from x where x >= 1 and x <= 10;
QUERY PLAN
`--SCAN TABLE x
addr opcode p1p2p3p4 p5 comment
- - -- -
0 Init 0 16000 Start at 16
1 OpenRead 0 2 0 1 00 root=2 iDb=0; x
2 Noop 0 0 000 Begin WHERE-loop0: x
3 CursorHint 0 0 0 AND(GE(c0,1),LE(c0,10)) 00
4 Rewind 0 14000
5 Column 0 0 100 r[1]=x.x
6 Lt 2 131 (BINARY) 51 if r[1]r[3] goto 13
9 Noop 0 0 000 Begin WHERE-core
10 Column 0 0 400 r[4]=x.x
11 ResultRow 4 1 000 output=r[4]
12 Noop 0 0 000 End WHERE-core
13Next 0 5 001
14Noop 0 0 000 End WHERE-loop0: x
15Halt 0 0 000
16Transaction0 0 1 0 01 usesStmtJournal=0
17Integer1 2 000 r[2]=1
18Integer103 000 r[3]=10
19Goto 0 1 000
---
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
