Bug #68849

Unstable persistence handling of DateTime (don't get what you set)

Added by Sebastian Mazza over 4 years ago. Updated over 2 years ago.

Status:
Closed
Priority:
Must have
Assignee:
-
Category:
Extbase
Target version:
-
Start date:
2015-08-07
Due date:
% Done:

0%

TYPO3 Version:
7
PHP Version:
5.6
Tags:
Complexity:
Is Regression:
No
Sprint Focus:

Description

DATE or DATETIME database values are treated as UTC during the mapping of an object and the php default time zone (date_default_timezone_get()) is applied to it. E.g.: If your php default time zone is "Europe/Berlin" (timezone_type: 3), it is summer and you have stored 2015-08-07 13:00:00 in the Databse you will get a DateTime object with the date 2015-08-07 15:00:00.

This is only done if your dates are stored in a column of type DATE or DATETIME. If you store the dates in a column of type INTEGER as unix timestamp the date values are not converted. Therefore, changing the olumn type of the database changes the values you get from a repository. This, however, cannot be correct.

Because the TCE in the Backend does not convert any dates/time zones, you will see different times in FE and BE.

But all of that is not the biggest problem. What is really annoying is the fact that the DateTime objects are not converted back to UTC during saving to DB (persistAll ()). As a result, every time you read and write a DateTime object your Time is increased/ decreased by the time offset of your default php time zone (in my case 2 hours).

Example

DB value for start: 2015-08-07 13:00:00
DB value after execution the following piece of code: 2015-08-07 15:01:00

public function dateTestAction() {
        $obj = $this->operatingTimeRepository->findByUid(1);
        $obj->getStart()->add(new \DateInterval('P0Y0DT0H1M')); // add one minute
        $this->operatingTimeRepository->update($obj);
}

Involved code:

typo3/sysext/extbase/Classes/Persistence/Generic/Mapper/DataMapper.php
save to db: getPlainValue()
read from db: mapDateTime()

possible solution:

Remove the conversion during read from Database and let the extension programmer decide how to deal with time zones.
See attached patch.

related:

[[https://forge.typo3.org/issues/68651#note-1]]

patch.diff View - Patch that removes the time zone conversion during DB read (1.31 KB) Sebastian Mazza, 2015-08-07 18:06


Related issues

Related to TYPO3 Core - Bug #64953: dbType date and datetime fields are saved in wrong timezone Closed 2015-02-10
Related to TYPO3 Core - Bug #72878: wrong datetime handling, they are not UTC in db Closed 2016-01-21
Related to TYPO3 Core - Bug #68651: Datetime() properties have wrong timezone Accepted 2015-07-30
Duplicates TYPO3 Core - Bug #74376: Extbase persistence not working with datetime fields Closed 2016-03-06

History

#1 Updated by Nicole Cordes almost 4 years ago

  • Status changed from New to Needs Feedback

Hi Sebatian,

Thank you for your bug report.

Your problem should be solved with https://review.typo3.org/#/c/47136/

Can you please test this patch, which was also backported to 7.6 https://review.typo3.org/#/c/47846/ and give feedback if this solves your problem as well.

#2 Updated by Sebastian Mazza over 3 years ago

Hi Nicole,
thank you for this patch, it solves my Date/Time update problem with the Exbase DataMapper.

But, while testing, I found an other, in my opinion inconsistent behaviour with the backend.
If you store Date/Time values in the DB as UNIX Timestamp (int). The values, displayed in backend forms are increased by the time offset of your local Timezone. But if you store the Data/Time values in table columns of type datetime, the backend form displays the exact UTC time that are stored in the DB.
In other words: If you store your Date/Time values as UNIX Timestamps the frontend output is exact the same as the backend form output. But if you use datetime columns instead, your frontend output is increased by the time offset of your local time zone. (e.g. submit 27.06.2016 10:00 in backend, leads to an output of 27.06.2016 12:00 in the frontend)

#3 Updated by Riccardo De Contardi over 3 years ago

  • Status changed from Needs Feedback to New

#4 Updated by Mona Muzaffar almost 3 years ago

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

#5 Updated by Markus Klein over 2 years ago

  • Parent task set to #77562

#6 Updated by Markus Klein over 2 years ago

  • Related to Bug #68651: Datetime() properties have wrong timezone added

#7 Updated by Markus Klein over 2 years ago

  • Duplicates Bug #74376: Extbase persistence not working with datetime fields added

#8 Updated by Markus Klein over 2 years ago

Closing as duplicate of #74376.

The other mentioned issue is dealt with in #77562

#9 Updated by Markus Klein over 2 years ago

  • Status changed from New to Closed

#10 Updated by Markus Klein over 2 years ago

  • Parent task deleted (#77562)

#11 Updated by Markus Klein over 2 years ago

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

Also available in: Atom PDF