How BETWEEN operator works for querying dates on RangeKey

Question

I have a Table with range key having values in iso 8601 format.
And when query between dates with matching value up to day, the results are inconsistent, meaning
ex.

2023-01-02 13:12:00.123
2023-02-06 13:12:00.123
2023-03-06 13:12:00.123
2023-04-02 13:12:00.123
2023-05-02 13:12:00.123
2023-06-02 13:12:00.123
2023-11-10 13:12:00.123

query :
BETWEEN 2023-01-01 and 2023-11-10 returns
2023-01-02 13:12:00.123
2023-02-06 13:12:00.123
2023-03-06 13:12:00.123
2023-04-02 13:12:00.123
2023-05-02 13:12:00.123
2023-06-02 13:12:00.123

query 
BETWEEN 2023-01-01 00:00:00:000 and 2023-11-10 23:59:59:999 returns
2023-01-02 13:12:00.123
2023-02-06 13:12:00.123
2023-03-06 13:12:00.123
2023-04-02 13:12:00.123
2023-05-02 13:12:00.123
2023-06-02 13:12:00.123
2023-11-10 13:12:00.123

why is it so? for consistent and better result do we have to always include timstamp in date time queries?

thanks

Answer

These time values are just strings and the BETWEEN is done without any awareness that they're dates.

If you ask a dictionary for everything between "a" and "b" you get all the A words but not all the B words. If you asked a for everything between "a" and "bzzzzz" you'd probably get what you want.

Now, it would be simpler to say "a" to "b~" because a tilde character sorts after "z" and so that special word would appear between the last "b" word and the first "c" word.

For your case maybe you want between "2023-01-01" and "2023-11-10~" and since the tilde sorts after space this would cover all timestamps in 2023-11-10 inclusively.

How BETWEEN operator works for querying dates on RangeKey

Relevant content