Query MongoDB documents where min and max columns encompass a value

Question

My documents have the following structure, where start and end represent hours in the format of HH:MM:

{_id: 1, start: "11:30", end: "14:30" }
{_id: 2, start: "05:30", end: "11:30" }
{_id: 3, start: "14:30", end: "18:00" }
{_id: 4, start: "18:30", end: "23:59" }

I receive another HH:MM value. Then, I need to obtain the _id of the record where the value I got is between the start and end range.

For example, if I have the value 15:18, I will need to obtain _id = 3, because start > 15:18 and end <= 15:18.

The examples I've found show the opposite case (having the min/max values and querying which row has a cell between those values). This is an example, and this is another example, and another one.

score 0 · Accepted Answer · answered Oct 23 '18 at 16:52

0

You need $gte and $lte operators inside .find() method. Try:

db.col.find({ start: { $lte: "15:18" }, end: { $gte: "15:18" } })

answered Oct 23 '18 at 16:52

mickl

48,568
9
60
89

The issue is that `start` and `end` are stored as `String`, therefore, I cannot compare them like this. – Carrol Oct 24 '18 at 10:53
@Mel are you sure that simple strings comparison won't be working here ? Could you give me an example when it might fail ? – mickl Oct 24 '18 at 16:55
I tried the same data of the example, and it didn't work. – Carrol Oct 25 '18 at 09:57
1

@Mel please take a look here: https://mongoplayground.net/p/Ju6B_RW87ZH – mickl Oct 25 '18 at 10:04

Query MongoDB documents where min and max columns encompass a value

1 Answers1