Difference In Dates in Days - Cannot Subtract String From String Error

[BlackMage]

I am trying to query all results but subtracting the current date from a date stored in the database, and then comparing it to a stored number. The code I have looks like this and is run through the aggregation from work:

$ops = array(

 

     array(

         '$redact' => array(

             '$cond' => array(

                 'if' => array(  

                     '$gte' => array(array('$subtract' => array(  'new ISODate()' , 'new ISODate(last_interacted_date)')), 'reminder_interval')

                 ),

                 'then' => '$$KEEP',

                 'else' => '$$PRUNE'

             )

         )

     )

 );

$collection ->aggregate($ops);

I get the following error message:

Uncaught exception 'MongoResultException' with message 'localhost:27017: cant $subtract aString from a String'

How can I cast or subtract these as dates and get the days in between the dates to compare to another var

[Wan Bachtiar]

How can I cast or subtract these as dates and get the days in between the dates to compare to another var

Hi,

You could utilise MongoDB\BSON\UTCDateTime class to get a representation of BSON date in PHP.
For example:

/* reminder time in milliseconds */
$reminder_interval = 2*60*1000; 

/* Get current time in milliseconds */
$current_time = new MongoDB\BSON\UTCDateTime(microtime(true)*1000);

/* An example of last interacted time converted into BSON date */
$last_interacted_date = "2016-05-11T08:11:42Z";
$last_time = new MongoDB\BSON\UTCDateTime(strtotime($last_interacted_date)*1000);

$query = array(
           

array('$redact' =>
             array('$cond' => 
               array("if" => 
                 array('$gte' => 
                   array

(
                     array('$subtract' => array($current_time, $last_time)),
                     $reminder_interval

                   )
                 ),
                 "then" => '$$KEEP',
                 "else" => '$$PRUNE'
               )
             )
           )
         );

Note that the result of the subtraction above is in milliseconds, hence the $reminder_interval is also in milliseconds.

The above snippet was tested using MongoDB PHP driver v1.1, PHP 7.0 and MongoDB v3.2.

Regards,

Wan

​

[BlackMage]

That can work except this, the $last_interacted_date is stored in the db and is based upon the document because it represents a date when a user last interacted with the document. Should I be using $project or something similiar in this?

[Wan Bachtiar]

Should I be using $project or something similiar in this?

Hi,

You could use $project to create a new field that contains the time difference, which you could pass into the next aggregation operator. See Aggregation Pipeline Operators for more operators.

Uncaught exception ‘MongoResultException’ with message ‘localhost:27017: cant $subtract aString from a String’

Based on your PHP exception class MongoResultException, it seems that you are using MongoDB Driver (legacy). To match the driver version that you are using, the following example is written in MongoDB Driver (legacy) v1.6, PHP5 and MongoDB v3.0.

The difference with the previous example is the use of MongoDate class instead of MongoDB\BSON\UTCDateTime class.

/* reminder time in milliseconds */

$reminder_interval = 60 * 1000;

/* Get current time in milliseconds */

$current_time = new MongoDate();
$query = array(
            array('$project'=>
              array('_id'=> 1,
                    'other_fields'=> 1,
                    'time_diff'=> array('$subtract'=>
                      array($current_time, '$last_interacted_date_field')
                    )
              )
            ),
            

array('$redact' =>
              array('$cond' => 
                array("if" => 
                  array('$gte'

 => 
                    array('$time_diff',
                          

$reminder_interval
                    )
                  ),
                  "then" => '$$KEEP',`
                  "else" => '$$PRUNE'
                )
             )
           )
        );

If you have further questions, could you provide the following:

• MongoDB version.
• MongoDB PHP driver version.

Kind regards,

Wan.

​

​