A Query on columns of type DATE results in an empty resultset
I'm working with extbase and created a matching like that:
$constraint = array(); $constraint = $query->greaterThanOrEqual('day', $monthBegin->format('Y-m-d')); $constraint = $query->lessThan('day', $monthEnd->format('Y-m-d')); return $query->matching($query->logicalAnd($constraint))->execute();
But after initializing the QueryResult-Object the resultset is always empty.
The problem seems to be in the new Typo3DbQueryParser:
case QueryInterface::OPERATOR_GREATER_THAN_OR_EQUAL_TO: $value = $this->queryBuilder->createNamedParameter($this->dataMapper->getPlainValue($value), \PDO::PARAM_INT); $expr = $exprBuilder->comparison($fieldName, $exprBuilder::GTE, $value);
As I work with greaterThanOrEqual the core expects that my date value is of type INT.
The resulting query looks like that:
WHERE ((`tx_events2_domain_model_day`.`day` >= 2016-11-01) AND (`tx_events2_domain_model_day`.`day` < 2016-12-01))
When I add the quotes my query works like expected.
#2 Updated by Stefan Froemken over 2 years ago
my fault. I have forgotten to say, that I use dbType => 'datetime' in TCA:
#5 Updated by Frans Saris over 2 years ago
Stefan, what mysql version are you using?
I just encountered something similar. On server 1 a query like
... WHERE model.reminder_send < 1451815237 works but on server 2 it doesn't. reminder_send is a datetime field here.
Difference between the 2 servers is:
- Server 1: MySQL 5.7.16-0ubuntu0.16.04.1
- Server 2: MariaDB 5.5.41-MariaDB
So it looks like MySQL server allows this to work where MariaDB doesn't handle this....
#6 Updated by Stefan Froemken over 2 years ago
On my MAC
Stefans-MacBook-Pro:bin stefan$ ./mysql --version
./mysql Ver 14.14 Distrib 5.6.28, for osx10.9 (x86_64) using EditLine wrapper
On our servers:
(13:37:08) [~] mysql --version
mysql Ver 14.14 Distrib 5.6.21, for Linux (i686) using EditLine wrapper
#9 Updated by Stefan Froemken over 1 year ago
Sorry. It didn't work.
In createTypedNamedParameter you call getPlainValue without second parameter which is important to check against dbType. As this is not given, the DateTime can't be converted to a string representation and falls back to int Timestamp.