Doctrine Mongodb ODM and DateTime Request

Question

I could use some help on this issue. I am creating an application using Symfony2 + mongodb + doctrine. I just want to use Doctrine ODM to query all users who are logged in in the last 5 minutes. I have a collection of users with a date field named date_last_login.

So, I am trying to use the query builder as follows:

<?php // Creating a DateTime object and susbtract 5 min from now // local time is 15:40:05, timezone: 'Europe/Paris' $_dateTime = new \DateTime(); $_interval5Min = new \DateInterval('PT5M'); $_dateTime->sub($_interval5Min); $query = $this->createQueryBuilder('User') ->field('date_last_login')->gte($_dateTime) ->getQuery(); ->execute(); 

When I looked at the compiled request using the symfony2 profiler, here is what I got:

 db.User.find({ "date_last_login": { "$gte": new Date("Fri, 23 Dec 2011 15:30:05 +0100") } }); 

It seems like the perfect exception that a date is 10 minutes earlier than 5 minutes? I just do not understand. If I reset my php DateTime object, the date is correct: 2011-12-23 15:35:05 (five minutes before 15:40).

So, I tried to collect the same query without deleting any minutes, and this time everything is fine:

 <?php // local time is 15:50:00 $query = $this->createQueryBuilder('User') ->field('date_last_login')->gte(new \DateTime()) ->getQuery(); ->execute(); // query is ok: db.User.find({ "date_last_login": { "$gte": new Date("Fri, 23 Dec 2011 15:50:00 +0100") } }); 

What am I doing wrong? Thanks for the help!

Answer 1

This is probably due to this PHP error, which was fixed in 5.3.3:

https://bugs.php.net/bug.php?id=50916

Answer 2

There are 3 ways to create a query builder for retrieving data when date_last_login fine than 5 minutes

1) create a DateTime object with your datetime format and get a timestamp from the DateTime object, then create a MongoDate object:

 $timeBefore5MinutesAgo = new \DateTime(date('Ymd H:i:s',\time() - 5 * 60)); $mongoDateBefore5MinutesAgo = new \MongoDate($currentDateWithTime->getTimestamp()); $query = $this->createQueryBuilder('User') ->field('date_last_login')->gte($mongoDateBefore5MinutesAgo) ->getQuery(); ->execute(); 

2) create a MongoDate object and use strtotime to convert the `datetime` format to timestamp :

 $mongoDateBefore5MinutesAgo = new \MongoDate(strtotime(date('Ymd H:i:s',\time() - 5 * 60))); $query = $this->createQueryBuilder('User') ->field('date_last_login')->gte($mongoDateBefore5MinutesAgo) ->getQuery(); ->execute(); 

3) only in the case of Doctrine 2 ODM , you can just create a DateTime object with your datetime format:

 $timeBefore5MinutesAgo = new \DateTime(date('Ymd H:i:s',\time() - 5 * 60)); $query = $this->createQueryBuilder('User') ->field('date_last_login')->gte($timeBefore5MinutesAgo) ->getQuery(); ->execute(); 

all three methods will create a request:

 db.User.find({ "date_last_login": { "$gte": new ISODate("2014-03-15T19:35:08+02:00") } });