Problem with query using $lt

Working with Data
1

I want to query a Date field. The query should return all the records that are between 2 dates. When I use the gt and lt operators I get an error in lt. This is the query I'm doing: var list = await db.saveNewRecord.find ({LastReportDate: { gt: ‘2021-03-15T21: 07: 48.000Z’}}, {LastReportDate: {$ lt: '2021- 03-15T21: 50: 48.000Z '}}).
And if I try to use $in, it doesn’t work.

Can you help me?

2

The operators are $gt (you are missing a $) and $lt (you have a white space between $ and lt).

Since you have 2 closing braces after the first date, then your query ends there. The 2nd LastReportDate… is taken as a projection or something else but it is not part of your query.

You have a couple of white space inside your date and time values that might cause wrong comparison.

3

Hi Steeve,

I try with this but it doesn’t work:

var list = await db.saveNewRecord.find({LastReportDate: {$gt: ‘2021-03-15T21:07:48.000Z’}, LastReportDate: {$lt: '2021-03-15T21:50:48.000Z '}}).

4

Hi @Norberto_Massaferro,

var list = await db.saveNewRecord.find({LastReportDate: {$gt: ‘2021-03-15T21:07:48.000Z’}, LastReportDate: {$lt: '2021-03-15T21:50:48.000Z '}}).

As Steve has mentioned, the second LastReportDate is not part of your query since it is taken as a projection.

There appears to be a white space between the Z and the ’ within your $lt value as well.

To better troubleshoot this, can you provide:

  1. The expected output document(s)
  2. The error you are receiving when running your commands.

Also, if you are querying a date object, you may wish to try something like this example below:

db.saveNewRecord.find({
	LastReportDate:{
		$gt:ISODate('2021-03-15T21:07:48.000Z'),
		$lt:ISODate('2021-03-15T21:50:48.000Z')
	}})

Hope this helps.
Jason

5

Hi Jason, thanks for the reply.

ISODate (‘2021-03-15T21: 07: 48.000Z’) did not work for me because the use of ISODate gave me an error.

But I understood what the problem was, the reality is that it was so basic that I overlooked it. The problem was in the {}, something that they mentioned to me and that although it was obvious I did not see it.

The sentence stays like this and it works:
list = await db.saveNewRecord.find ({LastReportDate: { gte: '2021-03-15T21: 07: 48.000Z', lte: ‘2021-03-15T21: 50: 48.000Z’}})

Thanks for the help.

Greetings

1 Like
6

If your query

works when dates are specified as string then your first post

is kind of misleading, Unless, of course, the node driver automatically converts string to Date. In the mongo shell it does not. See:

> c = db.date_test
db.date_test
> c.find() ;
{ "_id" : 1, "date" : ISODate("2021-03-18T18:47:46.276Z") }
> q = { "date" : { "$lt" : new Date() } }
{ "date" : { "$lt" : ISODate("2021-03-18T19:00:47.829Z") } }
> c.find( q ) ;
{ "_id" : 1, "date" : ISODate("2021-03-18T18:47:46.276Z") }
> q  = { "date" : { "$lt" : "2021-03-18T19:00:47.829Z" } }
{ "date" : { "$lt" : "2021-03-18T19:00:47.829Z" } }
> c.find( q ) ;
>

As for

I suspect that it is because you have extra spaces before 07 and 48. With the extra spaces:

> date = ISODate( "2021-03-15T21: 07: 48.000Z" )
2021-03-18T15:07:07.128-0400 E QUERY    [js] Error: invalid ISO date: 2021-03-15T21: 07: 48.000Z :
ISODate@src/mongo/shell/types.js:65:1
@(shell):1:8

and without the extra spaces

> date = ISODate( "2021-03-15T21:07:48.000Z" )
ISODate("2021-03-15T21:07:48Z")

So unless your data also has extra spaces, your query, despite not generating any error, probably produce the wrong result. See the following where my data is string and has no extra space but my query does.

> c.insert( { _id : 2 , date : "2021-03-18T19:00:47.829Z" } )
WriteResult({ "nInserted" : 1 })
> q = { date : { "$lte" : "2021-03-18T 19: 00:47.829Z" } }
{ "date" : { "$lte" : "2021-03-18T 19: 00:47.829Z" } }
> c.find(q)
> // string wise they are not equal but date wise they should but I get not result
> q = { date : { "$gt" : "2021-03-18T 19: 00:47.829Z" } }
{ "date" : { "$gt" : "2021-03-18T 19: 00:47.829Z" } }
> // now I got a result but I should not as the date are logically the same

All this simply to say that if you date related data, use Date rather than string.

1 Like
7

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.