#79613Saving wrong Date into DB if Field is of type DATE
Description
According to this Bugfix:
https://forge.typo3.org/issues/72878
I can Confirm the Problem from Viktor Livakivskyis comment:
> There is a special case with DB fields of type ‘date’.
>
> Imagine a case, when a DateTime object is created in “Europe/Berlin” like this
>
> $dateObject = new \DateTime(‘2016-07-13’);
>
> This will correspond to 2016-07-13 00:00:00.000000 in timezone “Europe/Berlin”.
> So, in case ExtBase will convert it to UTC during persistence, the date will correspond to 2016-07-12 22:00:00.000000 in UTC timezone. And saved to DB will result in “2016-07-12” stored.
>
> Then on retrieval from DB the DateTime object will be created and Timezone shift added, which will correspond to 2016-07-12 02:00:00.000000 in “Europe/Berlin”.
> One day is lost then.
>
> So, for case with native DB ‘date’ field the timezone shift should be totally ignored.
Please respect suggestion from “This Mächler”:
> Suggestion:
> Make it possible to choose how TYPO3 should treat dateTime values:
>
> 1. TYPO3 assumes that dateTime values from the DB are in local time and need no conversion from/to the FE or BE. This should be the default setting to avoid breaking a running system.
>
> 2. TYPO3 assumes that dateTime values are stored in UTC, conversion takes place from/to the FE or BE.
>
> This will not solve the problem with the already implemented workarounds. Maybe there is no solution for that.
Reviews 0
No change resolves or names this issue in its commit message, and no comment links one.
Related issues 6
- Bug #68651Datetime() properties have wrong timezone
- Bug #72878wrong datetime handling, they are not UTC in db
- Bug #79925Native date/datetime (TCA dbType) fields are not properly copied or localized
- Feature #80559datetime and time handling should always have timezone information
- Bug #80679Input field eval datetime in FlexForm converts to wrong timestamp
- Bug #91240Extbase DateTime with native dbType=datetime converts output differently
History 18
-
Sascha Rademacher commented <span data-processor="relativetime" data-value="2017-02-14T15:23:39+00:00" > 02/14/17, 15:23 </span>
I have a date field in the database. No matter what I do, when I read it, I always get the date, which is in the database, no matter what time zone it was before, like always UTC.
A pure date is always independent of time zones for me (human logic, not PHP logic). So I still do not understand if I want to save a datetime object to a date field, why there has to be a conversion, the only thing that can happen during the conversion is that the date is broken. There is no advantage in any case.
With the datetime type it is not necessary to create it in UTC because TYPO3 converts it to default timezone when reading from database. This is confusing and inconsistent. I understand this in principle but I mean it is wrong.And what about existing projects? I knew about the fact that TYPO3 stores dates time-zone independent or in other words, that TYPO3 does not convert dates before writing data to databse and therefore i wrote my code without converting anything / creating Dates in UTC.
Now I would have to adjust code in each project and unittests do no longer work anymore and I can not ensure that my changes work. -
Mona Muzaffar changed Related to #80679 <span data-processor="relativetime" data-value="2017-04-15T00:07:44+00:00" > 04/15/17, 00:07 </span>
-
Mona Muzaffar changed Related to #80852 <span data-processor="relativetime" data-value="2017-04-15T00:40:40+00:00" > 04/15/17, 00:40 </span>
-
Markus Klein changed parent task #81489 <span data-processor="relativetime" data-value="2017-06-06T18:15:55+00:00" > 06/06/17, 18:15 </span>
-
Markus Klein changed parent task #77562 <span data-processor="relativetime" data-value="2017-06-06T18:19:10+00:00" > 06/06/17, 18:19 </span>
-
Markus Klein changed Related to #72878 <span data-processor="relativetime" data-value="2017-06-06T19:21:57+00:00" > 06/06/17, 19:21 </span>
-
Markus Klein changed category <span data-processor="relativetime" data-value="2017-06-06T19:24:28+00:00" > 06/06/17, 19:24 </span>
-
Markus Klein changed Related to #68651 <span data-processor="relativetime" data-value="2017-06-06T19:24:48+00:00" > 06/06/17, 19:24 </span>
-
Riccardo De Contardi changed
Related to #80852<span data-processor="relativetime" data-value="2017-06-07T13:03:25+00:00" > 06/07/17, 13:03 </span> -
Markus Klein commented and changed status
NewNeeds Feedback <span data-processor="relativetime" data-value="2017-06-08T00:38:03+00:00" > 06/08/17, 00:38 </span>Maybe somebody can rephrase this ticket to a shorter summary.
From what I understood by reading: Yes, if native date(time) datatype is used for date fields, extbase must take care when reading the value to convert it to a proper DateTime value that is representing the stored time in the default timezone.
-
Sascha Rademacher commented <span data-processor="relativetime" data-value="2017-06-12T07:38:32+00:00" > 06/12/17, 07:38 </span>
In summary: it should not be converted if the database field is a “date” (not datetime) field. The backconversion to the original date is not possible.
The use of the conversion should be switchable in the installtool to ensure the compatibility to already existing projects which handle conversions by themselfes. -
Alexander Opitz changed status
Needs FeedbackNew <span data-processor="relativetime" data-value="2017-11-01T09:27:55+00:00" > 11/01/17, 09:27 </span> -
Benjamin Franzke commented and changed status
NewClosed <span data-processor="relativetime" data-value="2025-03-18T20:27:38+00:00" > 03/18/25, 20:27 </span>This issue has been resolved in TYPO3 v11.0 with #91240.
Extbase DataMapper was adapted back then to format dates for native DATE/DATETIME fields directly, without converting to UTC timezone first, see: https://github.com/TYPO3/typo3/commit/a614783e5917d6086813872bf863a910d31d4b38#diff-7798ba426325d313972569e7d96b0c5d1975e63ab2038b8967516e4f1040876fL343-L741Please report back if you see this issue still happening with TYPO3 v12 or v13.
-
Benjamin Franzke changed Related to #91240 <span data-processor="relativetime" data-value="2025-03-18T20:27:47+00:00" > 03/18/25, 20:27 </span>
-
Benjamin Franzke changed status
ClosedResolved <span data-processor="relativetime" data-value="2025-03-18T20:34:34+00:00" > 03/18/25, 20:34 </span> -
Benjamin Franzke changed Duplicated by #79557 <span data-processor="relativetime" data-value="2025-03-18T20:34:47+00:00" > 03/18/25, 20:34 </span>
-
Benni Mack changed status
ResolvedClosed <span data-processor="relativetime" data-value="2025-12-02T14:36:43+00:00" > 12/02/25, 14:36 </span>
Hi,
to get the surrounding facts straight:
- You have a DB field that is not capable of storing time zone information (date)
- You create a date with timezone information
- You store that field and the timezone information is obviously lost
- When retrieving the data due to the lost timezone information the date is now wrong (which worked before by accident in case you created a date in the default timezone or anything “close enough”)
The change to convert all dates to UTC is meant to ensure consistent date handling throughout the core – especially because date fields don’t have timezone information – when displaying those dates later, we need “something” to assume as base. This could be:
- UTC always
- the php timezone
- the mysql / server timezone
- the timezone you set manually when creating the date
- the timezone on the client side (because he used a javascript datepicker)
only one of these options is in my eyes “safe” – and that is UTC always. To fix your issue I’d just use new \DateTime(‘2016-07-13’, DateTimeZone::UTC); – or am I missing something?
For migration of “old” stored dates I’d directly use an mysql script doing something along https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_convert-tz