Bug #69290

Epic #77562: Misbehaviors with datetime values and timezones

Dates get reduced by a day if before 1970

Added by David Bruchmann over 4 years ago. Updated over 2 years ago.

Status:
Closed
Priority:
Must have
Assignee:
-
Category:
FormEngine aka TCEforms
Target version:
-
Start date:
2015-08-24
Due date:
% Done:

0%

TYPO3 Version:
6.2
PHP Version:
5.5
Tags:
date, 1970, day
Complexity:
Is Regression:
No
Sprint Focus:

Description

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.


Related issues

Related to TYPO3 Core - Bug #61952: bug dbType for date before 01-01-1970 Closed 2014-09-29
Related to TYPO3 Core - Feature #61110: Support for timezones in all date fields in TYPO3 BE New 2014-08-21
Related to TYPO3 Core - Bug #37244: TCA date evaluation for dates lower 01-01-1970 fails Accepted 2012-05-17
Related to TYPO3 Core - Bug #73871: Problem with date Field Closed 2016-03-02

History

#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;
to
$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:
https://github.com/ADOdb/ADOdb/issues/144

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.

#4 Updated by Stefan Neufeind about 4 years ago

PS: There was hope that with UTC-timehandling to land in TYPO3 CMS 7 this might be solved. However it's quite certain that won't make it into CMS 7 anymore. So we need a regular bugfix here for the existing environment.

#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.

#6 Updated by David Bruchmann about 4 years ago

  • Target version deleted (6.2.16)

#7 Updated by Riccardo De Contardi about 4 years ago

  • Category set to FormEngine aka TCEforms

#8 Updated by Mona Muzaffar over 2 years ago

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

#9 Updated by Markus Klein over 2 years ago

  • Parent task set to #81489

#10 Updated by Markus Klein over 2 years ago

  • Parent task changed from #81489 to #77562

#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.

Thanks.

#12 Updated by Riccardo De Contardi over 2 years ago

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

#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.

#14 Updated by Markus Klein over 2 years ago

  • Status changed from Needs Feedback to Closed

Closing due to lack of response.

Also available in: Atom PDF