Symfony phpcr doctorine querybuilder failed comparing datetime field with time

[archiparikh]

Hi David,

I saw several symphony-cmf related blogs from you online. I have some issue with getting cmf content using phpcr doctorine query and comparing with published date and time.
Following query is looking at published start and end date but not the time. I want to get the exact content published at particular time of the day.

$date = new \DateTime;
$qb = $this->createQueryBuilder('d');
$qb->where()->eq()->field('d.isPublishable')->literal($isPublished);
$qb->andWhere()->lte()->field('d.publishStartDate')->literal($date);
$qb->andWhere()->gte()->field('d.publishEndDate')->literal($date);

$results = $qb->getQuery()->execute();

if($results->first()) {
return $results->first()->getBody();
}
return null;

If you have any idea about how to do this, please suggest.

Please note that I have the publish dates with time on form and when get them by query they show me the time associated. I am having trouble with only comparison with time.

Thank you,
Archi

[dbu]

the publish start and end date are stored as a full timestamp afaik. when you reload after storing, is the time kept or does it get lost? what do you get when you use doctrine:phpcr:node:dump --props on the document, do you see the time as well? what do you get when you instead of the execute do echo $qb->getQuery()->getStatement()?

ftr: the sonata admin extension is only about day: https://github.com/symfony-cmf/core-bundle/blob/master/Admin/Extension/PublishTimePeriodExtension.php#L51 - but archiparikh says he has time as well in his form.

[dbu]

no updates for half a year