Query between two dates

Working with Data
,
1

I’m unable to understand how to create a query with dates to get documents. I’ve the following document:

{ "_id" : ObjectId("634675b9aa49cf504848e414"), "userId" : "5a26886656330579dc22009c", "userFullName" : "MarioRossi", "startDate" : ISODate("2022-11-02T23:00:00Z"), "endDate" : ISODate("2022-11-03T23:00:00Z"), "requestDate" : ISODate("2022-10-12T08:07:19.931Z"), "approved" : true }

if I query using:

db.plan.find({“startDate” : {$gte: ISODate(“2022-11-02T00:00:00Z”)}, “endDate”: {$lte: ISODate(“2022-11-03T23:00:00Z”)}})

I obtain the document. But if I add a day to startDate

db.plan.find({“startDate” : {$gte: ISODate(“2022-11-03T00:00:00Z”)}, “endDate”: {$lte: ISODate(“2022-11-03T23:00:00Z”)}})

I don’t obtain the document. In my mind, I think that 2022-11-03T00:00:00Z is greater than 2022-11-02T23:00:00Z. Where am I wrong?

2

It is. That is why you do not get the document. In the document startDate is

and your query means find document where the startDate (of the document) is greater or equal to 2022-11-03T00:00:00Z.

1 Like
3

You are ready. My mistake.

I’ve to check if every day of the month is in the interval between startDate and EndDate. How can I do that?

4

I do not understand exactly what you want. What do you mean by every day of the month? Is the month the query or the store dates? Is startDate and endDate fields of your document or input for your query? Share sample documents and query dates.

5

The document is the following:
{ "_id" : ObjectId("634675b9aa49cf504848e414"), "userId" : "5a26886656330579dc22009c", "userFullName" : "MarioRossi", "startDate" : ISODate("2022-11-02T23:00:00Z"), "endDate" : ISODate("2022-11-03T23:00:00Z"), "requestDate" : ISODate("2022-10-12T08:07:19.931Z"), "approved" : true }

In my program, I loop over all days in the current month and I’ve to check if the date is between startDate and EndDate.

The following is a peace of code:

for(int day=1; day<= howManyDays; day++) {
            LocalDateTime date1 = LocalDateTime.of(year, month, day, 00, 00).minusDays(1);
            LocalDateTime date2 = LocalDateTime.of(year, month, day, 23, 00).plusDays(1);
            Date d1 = Date.from(date1.atZone(defaultZoneId).toInstant());
            Date d2 = Date.from(date2.atZone(defaultZoneId).toInstant());
            List<Plan> plan = planRepository.findByDateRange(userId, d1, d2);
....
}

The wrong query is the following:

@Query("{'userId': ?0, 'startDate': {$gte: ?1}, 'endDate': {$lte: ?2}}") List<Plan> findByDateRange(String userId, Date date1, Date date2);

6

Why do you loop over all days to see if each is between startDate and endDate?

I think that if the first of the month is $gte than startDate and the last of the month is $lte than endDate then all days are between startDate and endDate.

Dates are stored UTC and you use LocalDateTime may this is why you do not get the result you wish.

I added JAVA tag to your post since this code looks like JAVA.

7

I’ve two documents: plan and presence. When a user wants to go on holiday, the program register the days range in plan as: { “_id” : ObjectId(“634675b9aa49cf504848e414”), “userId” : “5a26886656330579dc22009c”, “userFullName” : “MarioRossi”, “startDate” : ISODate(“2022-11-02T23:00:00Z”), “endDate” : ISODate(“2022-11-03T23:00:00Z”), “requestDate” : ISODate(“2022-10-12T08:07:19.931Z”), “approved” : true }

in presence the user register his timework as:
{ “_id” : ObjectId(“637b947c4e70fa4c14f0d2e6”), “userId” : “5a26886656330579dc22009c”, “month” : 11, “day” : 9, “year” : 2022, “entranceTime” : “08:30”, “breakStart” : “12:30”, “breakEnd” : “13:00”, “exitTime” : “17:00”, “totalWorkHours” : "8:0 }

So, for each day of the month, I’ve to check if the user is on holiday or not. I do that recreating the date as
LocalDateTime date1 = LocalDateTime.of(year, month, day, 00, 00).minusDays(1);
LocalDateTime date2 = LocalDateTime.of(year, month, day, 23, 00).plusDays(1);

and querying if the date1 and date2 are in the range of startDate and endDate in plan document.

8

I solved using another approach.

9

It would be nice if you could share how you solved it.

That would benefit all including the new user that has something similar in

10

When users ask for holidays, the program adds one or more entries in the presence collection also. So I haven’t to query for dates range in the plan collection because I’ve already one or more documents with the information in presence collection.

1 Like
11

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