PHP Driver: Problems with $search date range queries

Hi,

We’ve got a project using some PHP code with the PHP MongoDB Driver.
We’ve been trying to use $search with a range query over dates.
Like this:

...
{

           range: {

             path: "startDate",

             gte: new Date("2022-02-23T00:00:00+00:00"),

           },

         },
....

This works fine in Composer.
However, the php driver’s aggregate command only takes a PHP Array for the query.

Of course, this won't work.
['gte' => new Date("2022-02-23T00:00:00+00:00")]

The PHP BSON UTC Date Time doesn’t work either.
In fact, if we put what it generates into Composer, it doesn’t like it too.

For example, with the generated BSON value, the query is this:

{
            range: {
              path: "startDate",
              gte: {
    			"$date": "2022-12-13T10:30:00.000Z"
  		} 
              ,
            },
          },

But the error is compound.must[2].range.gte.type is required.
It looks like the range.gte doesn’t understand this BSON Type, but it does work with new Date() and new ISODate()

Anyone come across this before?
How to specify a MongoDB ‘new Date()’ when using the PHP driver?

In the MongoDB shell, Date() and ISODate() can be used to construct date objects (see: Date() in the shell documentation); however, those functions are unrelated to PHP or its driver. In a subsequent example, I see you used $date, which appears to be Extended JSON syntax (also unrelated to PHP).

To clear up any misunderstanding, I would suggest you start by familiarizing yourself with the BSON Types in MongoDB. There is a particular BSON type (0x09) that corresponds to a UTC date time value, which is not to be confused with the Timestamp BSON type (0x11), which is used internally for things like replication.

In the MongoDB shell, Date() and ISODate() can both be used to construct JavaScript objects that will encode as UTC date times in BSON. In the PHP driver, the MongoDB\BSON\UTCDateTime object serves that purpose.

Independent of the MongoDB driver, PHP itself has a DateTime object, which represents a date with a timezone. Since the MongoDB date type assumes a UTC time zone, the PHP driver allows you to construct a UTCDateTime object from a PHP DateTime instance; however, the driver will not automatically convert a PHP DateTime into a BSON date for you. If given a PHP DateTime, the standard rules for encoding PHP objects to BSON will apply (i.e. only its public properties will be saved as document fields).

How to specify a MongoDB ‘new Date()’ when using the PHP driver?

Please review the examples on MongoDB\BSON\UTCDateTime::__construct(), which demonstrate constructing a UTCDateTime instances from an integer, DateTime object, and without arguments.

If you are starting from a date string value, then you’ll always want to review DateTime::__construct() or createFromFormat(). Since the PHP driver allows construction of a UTCDateTime from a PHP DateTime, it does not reimplement the logic for parsing a date from a string.


This works fine in Composer.

Note that Composer is a package manager. I’m not sure if you meant to refer to the MongoDB PHP Library, which is distributed as a Composer package, but it helps to use to correct terms to avoid any misunderstanding.

Lastly, my replies in mongodb/mongo-php-driver#187 may also be helpful to clear up some misunderstandings. That’s an old issue, but it pertains to some users attempting to use ISODate() and JSON syntax with the PHP driver.

Thanks for your reply, but it is the MongoDB\BSON\UTCDateTime class that is creating the extended json, which the range query does not like.

Unfortunately I don’t have access to the code the developer is having problems with right now.
But the aggregation pipeline being passed into the driver is something like this:

$aggregate = [
  [ "$search" => [
      "index" => "default",
      "range" => [
         "path" => "startDate",
         "gte" => new MongoDB\BSON\UTCDateTime($search_date->getTimestamp())
       ]
    ]
  ]
]

This causes range.gte.type is required error.

We have tried all formats of dates passed into the gte.
It is failing to recognise MongoDB\BSON\UTCDateTime as a BSON date.

When we dump out the aggregate pipeline array, that’s where we see the EJSON.
However, if the PHP driver is just sending EJSON over to the gte statement, then it’s not working with it.

Typo while multi-tasking. It’s meant to say Compass.

Update from this morning after talking to the developer.
No exception is being thrown from the mongodb php driver. However, the pipeline is ignoring the range query.

image

MongoDB\BSON\UTCDateTime takes milliseconds as parameter while getTimestamp() will return value in seconds. Can you try updating the parameter being passed to MongoDB\BSON\UTCDateTime - making sure it is in milliseconds?

Thanks for all the replies.
We found that the developer was not passing the array into the driver. Instead, he was JSON encoding it, so of course, UTCDateTime output the EJSON, which the gte does not support in the $search range statement.

With that resolved, we still have a problem with one date field. However, we have a MongoDB consultant now on site to help.