Epic #77562: Misbehaviors with datetime values and timezones
Dates get reduced by a day if before 1970
Dates in the date-field are reduced each time on saving by a day if they are before 1970.
This is at least the behavior in Windows 7.
#1 Updated by Nils Schmidt over 4 years ago
I've same problem.
The date is correctly stored in the database.
At viewing in BE form of value output is false.
I think the problem is at render the dbType field.
I fix the problem in typo3\sysext\backend\Classes\Form\DataPreprocessor.php
function renderRecord with change line 199
$row[$column] = !empty($row[$column]) && $row[$column] !== $emptyValue ? strtotime($row[$column]) : 0;
$row[$column] = !empty($row[$column]) && $row[$column] !== $emptyValue ? strtotime($row[$column] . ' GMT') : 0;
so, the strtotime function return a timestamp with GMT time and not local timezone.
I hope it's helpful patch
#2 Updated by David Bruchmann over 4 years ago
Thanks for digging a bit in the issue.
I think the general approach has to be more complicated as the solution might work for you in GMT-timezone but the general fallback timezone is UTC if nothing is configured in php.ini.
Having verified the functions in adodb-time.php I remarked a special issue by executing the tests and I think the result might concern php in general perhaps and therefore TYPO3 too:
Having adjusted the timezone to Europe/Berlin and executing the tests there is one long formatted string echoed two times and the results for that time differ because the timezone seems being converted internally once to CET and once to CEST:
date CET 1970-01-01 01:00:00 am AM 01 Thu January 1 1 01 01 00 1 Thursday 0 01 Jan 1 +0100 RFC2822 Thu, 01 Jan 1970 01:00:00 +0100 00 31 0 4 70 1970 0 3600 2003
date CEST 1970-01-01 01:00:00 am AM 01 Thu January 1 1 01 01 00 1 Thursday 0 01 Jan 1 +0100 RFC2822 Thu, 01 Jan 1970 01:00:00 +0100 00 31 0 4 70 1970 0 3600 2003
More about this bug can be found here, till now I never verified why that happens exactly:
Apart from that a different timezone shouldn't be a reason usually that a whole day is missing, I'd expect 1 or a few hours maximum.
Nevertheless, I'll try your solution and think a bit how to track it down more precisely.
#3 Updated by Stefan Neufeind about 4 years ago
The problem here is that the date gets transformed from a timestamp to a date and back. During this it can happen that an hour or two are subtracted giving you a date/time on the day before. The time is then stripped, which gives you the day before at midnight. The same happens on each subsequent save.
#5 Updated by David Bruchmann about 4 years ago
- Target version set to 6.2.16
Ok, thanks for the feedback.
I'd like to see an approach like in AdoDB. The script there is still a bit buggy and could calculate with dates before 0 AD/BC I suppose.
I know Xavier included already maximal support concerning MySQL but that's ending backwords at year 1000 AD - that's a MySQL issue.
If dates before ar needed it would be possible to add an extra field as then the whole calculations have to be done in PHP while for newer dates / datetimes MySQL can do it with the internal functions.
How the older dates are saved then is another question, it would be nice having a string in readable format while AdoDB is working with floats (instead of integer-timestamps).
I know it's an annoying issue but I think it's really blaming for IT that everything before 1970 still makes some problems. And as historical knowledge is using more and more older dates my wish hopefully is reasonable.
#11 Updated by Markus Klein over 2 years ago
- Status changed from New to Needs Feedback
Is this still an issue? My tests with the latest v7 didn't show issues.
If so, please post your full TCA configuration (or the field you are using for the tests) and the SQL field definition.
And a step-by-step reproduction manual.
#13 Updated by Riccardo De Contardi over 2 years ago
I tried using EXT:news and its field "Date & Time", filling it with: "00:00 06-09-1969"
and the date has been saved correclty, both for TYPO3 7.6.22 and 8.7.5
Do you think it is sufficient to consider this closed or a different test should be made? Thank you.