CODEWITHSUNDEEP
mongodbmongodb-query
Prayag k
Prayag k

Reputation: 678

How can I compare two times in mongodb query?

I have two fields timeIn and arrivalTime in my mongodb collection. The timeIn field represents the time in which a user should arrive, and the arrivalTime represents the actual time of arrival.

timeIn:"2020-05-24T18:31:00.000Z",
arrivalTime:2020-05-27T02:36:37.558+00:00

How can I compare timeIn and arrivalTime so that I can mark those who arrives after timeIn as 'Late'.

$project: {
    name: 1,
    isPresent: {
        $cond: {
            if: {
                $lte: [ISODate("$arrivalTime"), ISODate("$timeIn")]
            },
            then: true,
            else: false
        }
    }
}

Upvotes: 2

Views: 1199

Answers (1)

Tom Slabbaert
Tom Slabbaert

Reputation: 22296

If you're storing the dates as string you can compare the two (as strings) assuming you ensure they are always in the same format and timezone.

$project: {
    name: 1,
    isPresent: {
        $cond: {
            if: {
                $lte: ["$arrivalTime","$timeIn"]
            },
            then: true,
            else: false
        }
    }
}

With that said this is not recommended as "ensuring" such things I feel is not a good approach. So what else can you do?

  1. Save the dates as Date. this will require you to update your code and database but will be In my opinion the best long term solution.
  2. If you're using Mongo version 3.6+ you can use Mongo's $dateFromString
$project: {
    name: 1,
    isPresent: {
        $cond: {
            if: {
                $lte: [{$dateFromString: {dateString: "$arrivalTime"}}, 
                       {$dateFromString: {dateString: "$timeIn"}}]
            },
            then: true,
            else: false
        }
    }
}

Upvotes: 2

Related Questions