Better way to get range of dates

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Better way to get range of dates

Cecil Westerhof-5
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Better way to get range of dates

Keith Medcalf

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



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Better way to get range of dates

Cecil Westerhof-5
2018-08-30 8:13 GMT+02:00 Keith Medcalf <[hidden email]>:

>
> 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.
>

That is the primary reason to use BETWEEN, but it does not hurt that it is
more efficient. ;-)



> sqlite> select x from x where x between 1 and 10;
> QUERY PLAN
>

I should learn to read QUERY PLAN's.

--
Cecil Westerhof
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users