TYPO3 Core

For easier/faster reproducing, I created two ddev projects with full configured setup incl. the extensions:
https://github.com/Gernott/hour_10
https://github.com/Gernott/hour_11

Looks more like there was a bug in v8.

v9+ do the right thing: storing datetimes "as they are", as there is no timezone information in a DATETIME field in MYSQL.
(in contrast to timestamps, which are UTC by definition)

You added examples for v10. Is v10 and v11 different?

This was a wrong extbase behaviour fixed with Core v11.1

https://review.typo3.org/c/Packages/TYPO3.CMS/+/64053

I think this needs some documentation (Breaking rst?) and a migration task.

If I didn't overlook something the migration task could be a CLI command,

• DB table to process
• timezone to use - the one used when the date data has been created (optional; default = current TZ of PHP)

• skim TCA of given table for DATETIME fields, for each field:
• correct the timezone offset in PHP
• update value in the DB

The correction must probably be like:

$utcDateTime = new \DateTime($currentDbValue, new \DateTimeZone('UTC'));
$originalTimeZone = new \DateTimeZone($tzFromCommandArgs ?? date_default_timezone_get());
$convertedBValue = $utcDateTime->setTimezone($originalTimeZone)->format('Y-m-d H:i:s');

Note: the format of course depends on the actual TCA type

I see a Problem with a converter. Example:
UTC from old TYPO3 -> CET+CEST mix in 11.5:
2021-10-31 00:30:00 -> 2021-10-31 02:30:00
2021-10-31 01:30:00 -> 2021-10-31 02:30:00

With other words: In the new way, I cannot say, when this was: 2021-10-31 02:30:00
With "Europe/Berlin" it exists in CET and in CEST. So this crashes the timetracking in my extension.

Perhaps there should be also a migration Task to timestamp?
Or extend the Date field with timezone information?
Or rollback to UTC in the database and adapt the Backend FormEngine to show also the local time?

• If the user selected 2021-10-31 04:00:00 it would actually store 2021-10-31 03:00:00 (-1 hours due to TZ+1, no DST)
• If the user selected 2021-10-31 02:00:00 it would actually store 2021-10-31 00:00:00 (-2 hours due to TZ+1 and DST)

So doing the reverse calculation as outlined above this should work correctly.
What am I overlooking? Or, where do you see a problem?

After a short conversation with Gernot, we could clarify that my suggestion for a CLI task should indeed be sufficient.

But, depending on the use case, it would also make sense to migrate those former DATETIME fields to an int-based timestamp for v11, as the former Extbase
application could have used the native datetime under the assumption that times are UTC based, which was only true (due to the bug) for Extbase, but nowhere else.
Such an migration would be more complex, as it would need an additional field in the DB first, where the converted value can be stored. After which the old field could be dropped an the new field could be renamed. Additionally, the TCA needs to be adjusted for the field then to use timestamps.
This is nothing the Core can offer as a one-off solution though.

Takeaways from our meeting:

• Create documentation for the issue (ReST file)
• Create the migration command

How I switched from dateTime to timestamp:

- remove this line in TCA: 'dbType' => 'datetime',
- change field type in ext_tables.sql to: myfield int(11) NOT NULL DEFAULT '0'

convert the SQL data:

SET GLOBAL time_zone = 'Europe/Berlin';
SET time_zone = 'Europe/Berlin';

ALTER TABLE tx_myext_domain_model_mytable ADD temp INT(11) NOT NULL AFTER myfield;
UPDATE tx_myext_domain_model_mytable SET temp = UNIX_TIMESTAMP(CONVERT_TZ(myfield, 'UTC', 'Europe/Berlin'));
ALTER TABLE tx_myext_domain_model_mytable CHANGE myfield myfield_old DATETIME NULL DEFAULT NULL; 
ALTER TABLE tx_myext_domain_model_mytable CHANGE temp myfield INT(11) NOT NULL;