CODEWITHSUNDEEP
sqlite
Brian
Brian

Reputation: 1989

Why is my SQLite Query using "less than or equal to" not returning the "equal to" part, just the "less than" part?

I have a SQLite database with this data in it:

select Id,TimeStamp from MyTable;

ID  TimeStamp
1   2019-07-15T18:18:44.829Z
2   2019-07-15T18:18:53.251Z
3   2019-07-15T18:18:59.475Z
4   2019-07-15T18:19:15.139Z
5   2019-07-15T18:20:10.499Z
6   2019-07-15T18:20:28.91Z
7   2019-07-15T20:41:32.069Z
8   2019-07-15T20:41:42.344Z
9   2019-07-15T20:42:09.124Z
10  2019-07-15T20:42:26.806Z
11  2019-07-16T15:06:27.394Z
12  2019-07-16T15:06:39.871Z
13  2019-07-16T15:06:49.397Z
14  2019-07-16T15:06:56.887Z
15  2019-07-16T15:07:04.288Z
16  2019-07-16T15:07:34.156Z
17  2019-07-16T15:07:56.606Z
18  2019-07-16T15:08:23.908Z
19  2019-07-16T15:08:51.135Z
20  2019-07-16T15:09:29.955Z
21  2019-07-16T15:09:57.523Z
22  2019-07-16T15:10:17.277Z
23  2019-07-16T15:10:43.056Z
24  2019-07-16T15:10:53.924Z
25  2019-07-16T15:11:49.575Z
26  2019-07-16T15:12:39.563Z
27  2019-02-01T15:07:04.288Z
28  2019-02-02T15:07:04.288Z
29  2019-02-03T15:07:04.288Z
30  2019-02-04T15:07:04.288Z
31  2019-02-05T15:07:04.288Z
32  2019-03-06T15:07:04.288Z
33  2019-03-07T15:07:04.288Z
34  2019-03-08T15:07:04.288Z
35  2019-03-09T15:07:04.288Z
36  2019-03-10T15:07:04.288Z
37  2019-04-11T15:07:04.288Z
38  2019-04-12T15:07:04.288Z
39  2019-04-13T15:07:04.288Z
40  2019-04-14T15:07:04.288Z
41  2019-04-15T15:07:04.288Z
42  2019-05-16T15:07:04.288Z
43  2019-05-17T15:07:04.288Z
44  2019-05-18T15:07:04.288Z
45  2019-05-19T15:07:04.288Z
46  2019-05-20T15:07:04.288Z

As you can see, the first dates inserted into the db (ID 1-26)) are within the period 2019-07-15 to 2019-07-16. The later dates (ID 27-46) are within the period 2019-02-01 to 2019-05-20.

However, when I run a query with "less than or equal to" in it, it only returns the less than data:

SELECT ID,TimeStamp FROM MyTable WHERE TimeStamp >= '2019-02-03' AND TimeStamp <= '2019-07-15' order by TimeStamp;
SELECT ID,TimeStamp FROM MyTable WHERE TimeStamp >= date('2019-02-03') AND TimeStamp <= date('2019-07-15') order by TimeStamp;
SELECT ID,TimeStamp FROM MyTable WHERE TimeStamp BETWEEN '2019-02-03' AND '2019-07-15' order by TimeStamp;
SELECT ID,TimeStamp FROM MyTable WHERE TimeStamp BETWEEN date('2019-02-03') AND date('2019-07-15') order by TimeStamp;

29  2019-02-03T15:07:04.288Z
30  2019-02-04T15:07:04.288Z
31  2019-02-05T15:07:04.288Z
32  2019-03-06T15:07:04.288Z
33  2019-03-07T15:07:04.288Z
34  2019-03-08T15:07:04.288Z
35  2019-03-09T15:07:04.288Z
36  2019-03-10T15:07:04.288Z
37  2019-04-11T15:07:04.288Z
38  2019-04-12T15:07:04.288Z
39  2019-04-13T15:07:04.288Z
40  2019-04-14T15:07:04.288Z
41  2019-04-15T15:07:04.288Z
42  2019-05-16T15:07:04.288Z
43  2019-05-17T15:07:04.288Z
44  2019-05-18T15:07:04.288Z
45  2019-05-19T15:07:04.288Z
46  2019-05-20T15:07:04.288Z

No matter which query I run, I never get the data equal to 2019-07-15, which I would think I should as I am using "less than or equal to".

Why is this not working?

Upvotes: 0

Views: 100

Answers (2)

forpas
forpas

Reputation: 164099

SQLite stores the date values as TEXT.
So this condition:

TimeStamp <= '2019-07-15'

compares TimeStamp against '2019-07-15' as strings.
What you can do is replace it with 

TimeStamp <= '2019-07-15T23:59:59.999Z'

so you get all dates up to (and including) '2019-07-15'.
Or simpler:

TimeStamp < '2019-07-16'

Upvotes: 2

Broots Waymb
Broots Waymb

Reputation: 4826

It's been a while since I worked with SQLite, but my assumption here is that it's the time portion throwing your query off.

Comparing against just the date portion (such as '2019-07-15') is probably comparing to '2019-07-15T00:00:00.000Z'. In this situation, all of the rows with a date equal to '2019-07-05' are getting left out because their time portion puts them later in the day, thus actually greater than even though the dates are equal.

You could adjust your queries to just check that it is less than the next day (< '2019-07-16'), or add a time aspect onto the day you're currently using (<= '2019-07-15T23:59:59.999Z').

Upvotes: 2

Related Questions