[TYPO3-mvc] How to compare a DateTime object correctly in anExtbase query?
Jan Kornblum
jan.kornblum at gmx.de
Mon Jan 12 15:34:49 CET 2015
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
More information about the TYPO3-project-typo3v4mvc
mailing list