Bug #63482


Epic #77562: Misbehaviors with datetime values and timezones

Query constraint on a DateTime field with a DateTime argument should trigger date format

Added by Clément MICHELET over 9 years ago. Updated over 1 year ago.

Could have
Target version:
Start date:
Due date:
% Done:


Estimated time:
TYPO3 Version:
PHP Version:
Is Regression:
Sprint Focus:



I have a DateTime declared like this in TCA :

'dbType' => 'datetime',
'type' => 'input',
'size' => 12,
'eval' => 'datetime,required',
'checkbox' => 0,
'default' => '0000-00-00 00:00:00'

The SQL field is a native DateTime field (created from extension_builder).
When I apply a constraint on a query with an argument, It is automatically converted to an unix timestamp. My query doesn't works because it's expecting a native SQL datetime value instead of a timestamp. The workaround is to apply a format ->format("Y-m-d G:i:s") on the argument. This is ok but when you have to manipulate many datetime fields or have many queries, the human error factor is increasing.

I don't have digged enough inside the extbase query API to see if we have access to the field type at the moment when the query is build. But It could be great to see this API to take in charge the date formatting step.
I think that this change is a breaking change for all of those who already applied manually the format. It will require a setting to enable/disable this behaviour and by default , It will be disabled.

Related issues 2 (1 open1 closed)

Related to TYPO3 Core - Bug #78779: A Query on columns of type DATE results in an empty resultsetClosed2016-11-23

Related to TYPO3 Core - Bug #79473: Typo3DbQueryParser fails since Doctrine by comparing date fieldsNew2017-01-26

Actions #1

Updated by Georg Ringer over 9 years ago

  • Tracker changed from Feature to Bug
  • TYPO3 Version set to 6.2
  • Is Regression set to No
Actions #2

Updated by Benjamin Rau over 9 years ago

I can confirm the issue! And the workaround would make it impossible to change the dbtype later without changing all constraints..

Actions #3

Updated by Sebastian Mazza almost 9 years ago

I can also confirm the issue.

Actions #4

Updated by Felix Nagel over 8 years ago

Same issue when using dbType => date in TYPO3 7.6.2

Actions #5

Updated by Stefan Froemken over 7 years ago

  • TYPO3 Version changed from 6.2 to 7
  • Complexity set to medium

I currently have the same problem, but I don't see any chance to solve that with 2 or 3 lines of code.

I slightly see a possibility to change preparseComparison(). We can add $query or $source as third parameter, but IMO changing DateTime objects at that point is the wrong way.

A better place would be resolveParameterPlaceholders(), but at that point we don't have any information about colnames and classnames anymore. We need them to build a columnMap, which is currently not possible. To solve that we have to change many methodcalls and it seems more breaking than solving that problem.

I can confirm this problem for TYPO3 7.6, too.

Actions #6

Updated by Mona Muzaffar about 7 years ago

  • Related to Epic #80852: Datetime handling in backend added
Actions #7

Updated by Riccardo De Contardi about 7 years ago

  • Parent task set to #77562
Actions #8

Updated by Riccardo De Contardi about 7 years ago

  • Related to deleted (Epic #80852: Datetime handling in backend)
Actions #9

Updated by Georg Ringer over 6 years ago

  • Related to Bug #79473: Typo3DbQueryParser fails since Doctrine by comparing date fields added
Actions #10

Updated by Felix Nagel over 1 year ago

Still an issue in TYPO3 11.5.16 when using dbType => date.


Also available in: Atom PDF