Epic #77562: Misbehaviors with datetime values and timezones
Handling dates in the very past inside T3-Backend
I tried to store a date around 01.01.1800 in T3-Backend.
I wrote an extension but saving fails each time - as the date is not stored correctly.
I can't even say how T3 determines/converts the date. As the system stores something around 1936. Seems like some sort of converting error in T3-Backend. Same with dates in the very future.
If i set the 'date' field directly in the database to the correct date it won't be displayed at all.
Here are some additional informations:
geboren_am date DEFAULT '0000-00-00',
'geboren_am' => array(
'exclude' => 1,
'label' => geboren_am',
'config' => array(
'dbType' => 'date',
'type' => 'input',
'size' => 13,
'max' => 20,
'eval' => 'date',
'checkbox' => 0,
'default' => '0000-00-00'
#2 Updated by Riccardo De Contardi over 3 years ago
- Category set to FormEngine aka TCEforms
I add here the comment from #69287 to keep track of it:
To describe the problem a bit more:
The datepicker from ExtJS (implemented in TYPO3, at least 6.2) can handle dates back to year 0.
MySQL-datetime can handle dates starting with year 1000. This general option was implemented by Xavier already.
The Datetime-functions of AdoDB can handle years back to year 0, with adjustments probably further back too.
Some databases can handle dates that go further back than 0, i.e. PgSQL can handle approximative -4500
Some PHP-Functions might limit the handling of dates too.
I think a really cool approach would be to enable TYPO3 to take dates older than 0.
I planned working on it but never found the time yet and in the moment I never see any time-window in nearer future.
#4 Updated by David Bruchmann over 3 years ago
To understand what's happening it might be helpful to know the history how the current date-handling was first created:
It might be completely different now especially because of refactoring inside TYPO3.
#5 Updated by Christian Fries over 3 years ago
This problem still exists in TYPO3 8. And on top of that: If you try to save an empty date (empty field), you get an error:
1: These fields of record 2 in table "tx_extensionname_domain_model_person" have not been saved correctly: date_of_birth! The values might have changed due to type casting of the database.
#6 Updated by David Bruchmann over 3 years ago
I dare to say, the error is an issue with your extension. Probably it can be fixed by changes in database or TCA.
Have a look at the definitions in \ext_tables.sql and Configuration\TCA\mymodel.php above posted by Ralle Büchnitz, especially at 'default' and "geboren_am date DEFAULT '0000-00-00'".
#7 Updated by Christian Fries over 3 years ago
Hi David, I dare to say that's not true. The exact above configuration will not allow you to save a record (form engine, not extbase) with an empty "geboren_am" field in TYPO3 8.7.1.
If you don't belive me, check out the well known extension styleguide. When you try to save the demo record with no date in inputdatetime_2 (which has the same configuration we talk about here, see TCA and sql), you'll get the same error I get in my extension.
#8 Updated by Christoph Bessei over 3 years ago
I can confirm the problem of Christian.
Saving an empty date gives always the "The values might have changed due to type casting of the database." error.
It looks like the problem is some value casting, at least InputDateTimeElement.php always has $itemValue set to 0 instead of '0000-00-00' (which explains the error message).
#12 Updated by Riccardo De Contardi almost 3 years ago
I think it is still present in 8.7.9 - It is not possible to save a date prior to 01-01-1970 for example in the "start date" of fe_users. The error is still:
1: These fields of record 1 in table "fe_users" have not been saved correctly: starttime! The values might have changed due to type casting of the database.