[TYPO3-mvc] How to compare a DateTime object correctly in anExtbase query?

[Jan Kornblum]

Hi Stephan,

> Did you dig into extbase query building? What does the created MySQL query 
> look like and what's the difference to the query you expect?
>
> First: Using DateTime objects directly should be fine.
> Did you actually try just skipping the ->format() completely?

Without the format it doesn't work at all: findPrevious() alsway 
returns the last dated record, findNext() doesn't return any record.

> Second: Time zones are always messy.
> Unix timestamps (there are rumours this can be changed but I never did) are 
> UTC by definition. In contrast to that, "new \DateTime()" gives you the very 
> time zone defined in php.ini or the servers default time zone if no 
> configuration is set. So there are chances this is "GMT+1" on your server.

I don't create any DateTime manually at all: There is just the $post 
object (and its property datePublished is stored as DateTime in 
Extabase as well as in the database).

> You could either cast your DateTime object to UTC or return the unix time 
> stamp ("U") to compare. If you decide for making the DateTime UTC, keep in 
> mind this changes the nested property of your model. So if you pass that 
> $post to e.g. fluid later or hand it to an repository::update(), this could 
> change database contents on every click.

As the findPrevious() works fine using this:

$query->setOrderings(array('datePublished' => 
\TYPO3\CMS\Extbase\Persistence\QueryInterface::ORDER_ASCENDING));
$query->matching($query->greaterThan('datePublished', 
$post->getDatePublished()->format('Y-m-d H:i:s')));

...and findNext() always returns the same (not the next) object as the 
object given as param to compare against in in the query:

$query->setOrderings(array('datePublished' => 
\TYPO3\CMS\Extbase\Persistence\QueryInterface::ORDER_DESCENDING));
$query->matching($query->lessThan('datePublished', 
$post->getDatePublished()->format('Y-m-d H:i:s')));

...I believe that the problem is located in the fact, that the 
comparison isn't matching exactly (just nearly) because of the wrong 
format ("maybe some seconds / milliseconds are missing?")

Or might there be any bug in extbase when comparing DateTimes in a 
query?

Kind regards, Jan