TYPO3 Core

May I add the following thoughts...

I encountered the same problem. In my domain object I have a property of type \DateTime:

/**
 * @var \DateTime
 */
protected $sendDate = NULL;

Upon creating a new object I add a DateTime object:

$moment = new \DateTime('2015-08-05 10:00:00'); // timezone of server: Europe/Zurich
$object->setSendDate($moment);

After persisting this object I can read in my database (column-type: datetime) the exact same value: 2015-08-05 10:00:00

When I retrieve this object back from the database and var_dump the sendDate property, I get a \DateTime object with timezone 'Europe/Zurich'. But now the value of this objects sendDate-property is 2015-08-05 12:00:00 (!)

I'm therefore assuming that extbase expects a datetime database value to be in UTC and upon retrieving such a value is converting it to the "correct" timezone. The problem is now, that during the persisting process, that value never gets transformed to UTC!

Can confirm this problem in TYPO3 6.2.15... dangerous shit...

Just stumbled onto this bug too. Timezone is not respected when saving values to the database.
bump

I can't confirm this bug directly, but I can confirm that TYPO3 could have problems with DST. DST handling will work correctly, if Timezone-Type is of type 3 only.

$date1 = new \DateTime(date('c', $value)); // TYPO3 way: TZ type 1 with +02:00
$date2 = new \DateTime('@' . $value); // previous version in TYPO3: Plain UTC. Difference is always +00:00. So time will be set -2 hours for Germany
$date3 = new \DateTime(date('r', $value)); // Same result as date('c')
$date5 = new \DateTime(date('Y-m-d H:i:s', $value)); // TZ type 3. No TZ given, so default TZ (Europe/Berlin) will be set
$date6 = new \DateTime(date('Y-m-d H:i:s e', $value)); // e represents TZ Europe/Berlin. TZ type 3.
$date7 = new \DateTime(date('Y-m-d H:i:s P', $value)); // P represents TZ difference like +02:00. TZ type 1
$date8 = new \DateTime(date('Y-m-d T H:i:s', $value)); // TZ represents TZs like EST, MDT, CEST, CET. TZ type 2

To be more specific:

Version:
At the time of the bug report, July 2015, it was TYPO3 version 6.2.15

Domain model:

/**
 * firstDay
 *
 * @var \DateTime
 * @validate NotEmpty
 */
protected $firstDay = NULL;

TCA:

'first_day' => array(
   'exclude' => 0,
   'label' => 'LLL:EXT:bwoffers/Resources/Private/Language/locallang_db.xlf:tx_bwoffers_domain_model_offer.first_day',
   'config' => array(
      'type' => 'input',
      'size' => 10,
      'eval' => 'date',
      'checkbox' => 1,
      'default' => strtotime('next Monday'),
      'placeholder' => 'dd-mm-yyyy'
   ),
),

This annoying bug is not fixed in TYPO3 7.6.10

Fluid outputs time one hour later than backend.

I'm using version 7.6.10 and can confirm: This bug is NOT fixed yet! :-(

Please change the status of this issue! It is not nearly resolved! What a pain - unbelievable...

Hi Andreas,

we ran into a similar issue so I did some testing, it seems a date value is persisted (in \TYPO3\CMS\Core\DataHandling\DataHandler::process_datamap) after a conversion using `gmdate`. The value stored should be a unix time based on UTC, but when `\TYPO3\CMS\Extbase\Persistence\Generic\Mapper\DataMapper::mapDateTime` is used to convert the value the current timezone (in my case Europe/Amsterdam) seems to fail. Forcing the timezone to UTC fixes the issue when formatting the date.

The problem seems to occur if the TCA eval is `time`, not sure if all checks in the conversion process match. What also made me wonder was the comment "integer timestamps are local server time" in `mapDateTime()`, no further conversion takes place here.

Rgs,
Arno

I'm not sure, but I suspect this issue has had the unfortunate consequence of making our pages fail the JS validation each time they're saved.
Almost all our pages have starttime set. When we edit some field in the BE form (title f.ex.) and save, the starttime field will actually get the class of has-change, even though the value in the field doesn't appear to change at all.
Comparing the value in the BE form (and thus in $fieldArray) to the value in the DB shows a difference of one hour in the value.

To reproduce:
- Create a page with a title and starttime value (save and close)
- Open the page again, edit the title and save (just save, not save and close)
- Now check the starttime field - it will have a class of has-change.
- If you try and close the form now you will get a warning telling you there are unsaved changes. This is because the values in the form and DB don't match, I suspect.

See also #80008

Hello,

I guess this (8.7.4) observation is related:

I have a TCA with an input, eval = time.

In the backend I save 09:00.
In the database it writes 32400 (9*3600 => correct!)
In the frontend with f:format.date I get 10:00. Eh...

This seems to be no rocket timezone science, but a matter of simple math: Divide the value by 3600 for hours and the rest by 60 for minutes.

I assume the error occurs because it internally mistakenly uses some date object, which naturally assumes the 01.01.1970 for its math, which is a day in winter(!) and so currently one hour off in summer...

Can't f:format.date branch out differently, if the value is less than 86400? Or maybe provide a viewhelper f:format.time?

Greetings,
Manuel

I have investigated this issue yesterday a little bit deaper:

It is a little bit more than just a math Problem:

first influence:
php.ini -> What is the ORIGINAL @date_default_timezone_get()
test this in a separate php file not INSIDE of a TYPO3 Script as TYPO3 is changing this!

second influence:
TYPO3 Localconfiguration.php -> is $GLOBALS['TYPO3_CONF_VARS']['SYS']['phpTimeZone'] set?

third influence
during the TYPO3 Bootstrap Process @date_default_timezone_set() is Called.
it will be set either with $GLOBALS['TYPO3_CONF_VARS']['SYS']['phpTimeZone'], Value from php.ini or is set as Fallback to 'UTC'

last influence:
Extbase and Fluid Data mapper and the TYPO3 Core:
DateTime Values in the ExtBase Datamapper are in many Cases using as HARDCODED Fallback the UTC timezone , in Core some places uses $GLOBALS['TYPO3_CONF_VARS']['SYS']['phpTimeZone'] and on other places the value from @date_default_timezone_get()

Solution can be:
REMOVE all hard Coded 'UTC' calls and rely only @date_default_timezone_set() in EXTbase mapDateTime
or
Add a DateTime Parameter to the Fluid DateTime ViewHelper (best Approach in my opinion as with this, everyone can help himself in the end)

I Actually have
php.ini with "Europe/Berlin"

I Added this
$GLOBALS['TYPO3_CONF_VARS']['SYS']['phpTimeZone'] = "UTC"

ALL Entered Values in Frontend are now Wrong! -> I Fixed the wrong Date Fields with some SQL Statements so OLD Values in Frontend look correct

Entering a DateTime Value in the backend is now also shown Correctly in Frontend.
Also Summertime/Wintertime Values Are Working correctly.

The only problem with THIS Approach is comparasions between actual server time and database value ..
(f.e.
// has this event startet
$now = new \dateTime(), will deliver a UTC DateTime Value means actuall 2 hours too early.

So : if you have problems with this , check also your php settings and the TYPO value. Maybe setting ['phpTimeZone'] = "UTC" can help. but please check you already entered date Time values ( startdate, enddate of tt_content elements , pages, Event databes entry, scheduler tasks .. and more .. )

Hello Jörg (Velletti):

I have investigated this issue yesterday a little bit deaper:

Thanks a lot! That sounds like you did a major step in clarifying this issue!

But of course: If the logic in the core isn't uniform that needs correction!

maybe .. but finally changing the datamapper Code can brake dateValues on some users TYPo3 db if PHP.ini and TYPo3 setting ist diffent from UTC ..
For me only adding a timeZone="" parameter to the Fluid format.date() Viewhelper can be the best for all solution.

Why? I have a Server in Strassburg and reside in Munich. so the serverstimezone is Europe/Berlin.
but we have a spanish dependency, organizing online trainings for the spanish speaking Customers in Madrid. With Cusotmers in Gran canaria, Mexico ... .

They all get dateTime Values in Frontent for BERLIN .. (and have to calculate on their own)

We encountered this problem, too. But Fluid/Extbase seem to work correctly here. The problem is that the Backend has no possibility to configure/edit the timezone of time inputs.

Timestamps are seconds from 1970 when located on the prime meridian (UTC), but when displaying them on any other meridian steps of 3600 seconds have to be added/substracted (but only for display). But for eval = time fields no timezone considerations are done in the backend at all. So "9:00" is stored as 9*3600 regardless of where the backend user is located (i.e. what his browser sends for the region) or what the configured server timezone is. For eval = date or eval = datetime this timezone considerations seem to happen, however.

For PHP \DateTime objects there is no distinction between "date" and "time" and "datetime". A \DateTime object is only reperesented by an UTC timestamp + a timezone (which is correct: it has the actual time and information for a potential display offset). Extbase/Fluid are considering both correctly for \DateTime properties.

What actually has to happen to solve the issue is that all time fields in the backend (no matter what concrete "eval") always store times as UTC timestamps in the database and treat them as such when displaying them in the backend. An upgrade wizard has to be shipped to convert all "eval = time" fields at the same time.

Having UTC timestamp in the database can be useful (ex: "timezone independent sorting", changing the PHP default timezone).

I think the bug is in TYPO3\CMS\Extbase\Persistence\Generic\Mapper\DataMapper->mapDateTime().

The following lines discard timezone information:

        // integer timestamps are local server time
        return \TYPO3\CMS\Core\Utility\GeneralUtility::makeInstance($targetType, date('c', (int)$value));

The expression "date('c', (int)$value)" returns a string with the form "2020-06-17T17:45:53-04:00", where the timezone offset is present (and results int the infamous "timezone_type=1"), but not the real timezone identifier that is needed to handle daylight saving.

Theses lines should be replaced by:

        // integer timestamps are regular (UTC) timestamps
        $datetime = new \Datetime('@' . (int) $value);
        $datetime->setTimezone(new \DateTimeZone(date_default_timezone_get()));
        return $datetime;

With this patch, the \Datetime properties have the proper timezone, that allows regular manipulations.

There is no need to "upgrade" existing data.

POC: https://3v4l.org/Y51mk

That really sounds like a substantial answer. I'm hoping somebody can verify this and finally get it implemented.

What an odyssey since 2015!

Martin Bless wrote:

That really sounds like a substantial answer. I'm hoping somebody can verify this and finally get it implemented.

Not to shamelessly promote myself, but this is exactly what I proposed in March 2017 in
https://forge.typo3.org/issues/80349 and
https://review.typo3.org/c/Packages/TYPO3.CMS/+/52952

but it may need an update for current core version.

For db datetime fields i see the major problem in the backend editor. The extbase

DataMapper->mapDateTime()

Is there any update on this issue? It seems still being present... O.o

Several patches are addressing this, the important one being:
https://review.typo3.org/c/Packages/TYPO3.CMS/+/86888?usp=search

See that patch for some insights why it's not so easy to deal with.

Patch set 2 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/88836

Patch set 3 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/88836

Patch set 4 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/88836

I think the direction this patch is taking is wrong. As I wrote earlier, Extbase works correct !

• A timestamp field containing a full timestamp (so a datetime, seconds to then since 1970) stores the UTC seconds. All displayed dates are offsets by adding or substracting the difference via the given timezone. This is correct.
• A timestamp field containing only time is stored as seconds from 1970 in the current timezone. Which is wrong. If a time field would store UTC seconds as well, then everything would work whithout changing Extbase at all. The backend currently stores "9:00" as "9*3600" always (no matter the timezone) - which is wrong. To work correctly it would needs to add/substract the timezone offset first, just like it does already with "normal" datetime fields. Extbase or PHP Datetime do the timezone magic already for us (and they do it correct). Extbase / PHP DateTime just get a wrong time-timestamp passed from the DB, because it was stored without adding/subtracting the current timezone when persisting to DB by the DataHandler!

Instead of letting Extbase reverse the wrong core storage behavior ("fix a bug with another bug"), I'd heavily suggest to unify time and datetime handling in the Backend to make time and datetime work consistently and correctly!

S P wrote in #note-46:

• A timestamp field containing a full timestamp (so a datetime, seconds to then since 1970) stores the UTC seconds. All displayed dates are offsets by adding or substracting the difference via the given timezone. This is correct.

That sounds valid. All timestamps that are stored in database SHOULD be UTC.
Thereby the timestamps SHOULD be stored as unix timestamp (seconds since 1970).

Alternative:

The alternative is to store timestamps including their timezone designator.
Thereby the timestamps MUST NOT be stored in database as unix timestamp.
In this case timestamps SHOULD be stored in ISO 8601 date format including a timezone designator e.g. "2025-04-03T11:15:07+02:00"

See https://en.wikipedia.org/wiki/ISO_8601#Time_offsets_from_UTC

I do not recommend this alternative.

Ayke Halder wrote in #note-47:

The alternative is to store timestamps including their timezone designator.
...
I do not recommend this alternative.

Me neither. I recommend that the next LTS is treating TCA time fields identical to the (already correctly working) TCA datettime fields (DataHandler patch) and providing an upgrade wizard that applies the configured timezone ("add/substract n*3600") to all existing time fields in the SQL DB (which can be identified by looking into the TCA).

This would solve this issue robustly and consistently.

Sorry, Really hard to understand the reason for your comments.

the backend stores datetime Values in a format, depending on a setting in TYPO3
$GLOBALS['TYPO3_CONF_VARS']['SYS']['phpTimeZone']

if this is not set, it users the php/server configuration by :
date_default_timezone_get()

if you change this behavior, this would change tstamp, crdate or any other timestamp values.

the bug is:
the Fluid viewhelper format.date IGNORES
$GLOBALS['TYPO3_CONF_VARS']['SYS']['phpTimeZone']
and just relies on php/server configuration

so:
if someone has set

$GLOBALS['TYPO3_CONF_VARS']['SYS']['phpTimeZone'] = "UTC"
and
on his server
date_default_timezone_get() returns "UTC"

he is not affected.
But if someone has startet without setting one of this correctly (or has no access to it) the behave it in same way

the fluid viewhandler is behaving WRONG (as it ignores the TYPO3_CONF_VARS )

Jörg Velletti wrote in #note-49:

the backend stores datetime Values in a format, depending on a setting in TYPO3 $GLOBALS['TYPO3_CONF_VARS']['SYS']['phpTimeZone']

Maybe the ext:core behaviour changed since I last checked. Last time when I debugged in this area, I noticed that time values in SQL integer fields were persisted as "3600 * entered_time" (ignoring timezone, wrong), and datetime values in SQL integer fields were persisted as UTC timestamps (respecting timezone, correct). If this is already consistent in the Datahandler, fine (however, I wonder where the upgrade wizard for this is, then? Because such a change would have broken all legacy installations if time fields suddenly are intrepreted differently).

Looking at this (13 LTS code):
https://github.com/TYPO3/typo3/blob/13.4/typo3/sysext/core/Classes/DataHandling/DataHandler.php#L2042
I do not believe there was any change. Time is stored without respecting timezones. Datetime is stored with respecting timezones. The code comment even mentions this.

the fluid viewhandler is behaving WRONG (as it ignores the TYPO3_CONF_VARS )

Maybe, but then the provided patch would also be wrong, as it changes Extbase's ORM mapping and nothing regarding Fluid ViewHelpers. Extbase can be used without Fluid and vice versa.

For Fluid this is coming:
https://docs.typo3.org/c/typo3/cms-core/main/en-us/Changelog/14.0/Feature-92760-ConfigurableTimezoneForDateViewHelper.html#feature-92760-configurable-timezone-for-dateviewhelper

S P wrote:
A timestamp field containing only time is stored as seconds from 1970 in the current timezone. Which is wrong.

Well, its your definition that this is wrong, but I think your description of the behavior is not 100% accurate, therefore the conclusion is not meaningful.

Reason:
No one said time is stored as seconds from 1970, time in TYPO3 is stored as seconds since midnight of whatever day the time refers to.
Time is a value without date association, therefore any association to 1970 (in UTC or localtime) can only be a convention.
By definition there is no association to any date.

I agree that it is easer to map a time to DateTime object with 1970-01-01 if it were a UTC timestamp, but for FormEngine and DataHandler it simply is not for the described reasons.

So, why don't we change FormEngine and DataHandler for the sake of easier DateTime initialization?
I think this would be bad design, because DateTime is not at all a suitable representation for Time, we'd actually would like to have an object where the Date part could be omitted. Now, should we let database design be influenced by insatisfactory object representations? I don't think so...

What the extbase change (covered by a feature flag) will do is to map the time to the date 1970-01-01 localtime when converted into a \DateTime object.

I understand that there are arguments for and against, but a single time field, represented as integer, is intended to be seconds in TYPO3 (not my definition, it's just the status quo).

Btw, the Epic https://forge.typo3.org/issues/77562 has a datetimetest extension attached (years old, but serves as an example for good usecases) where a time field is mapped to an int property, and therefore does only make sense if interpreted as seconds since midnight.
Why this example? Interpreting time as seconds from 1901-01-01UTC makes only sense for interpretation as DateTime object. Any direct interpretation of the database value would expect the time to be seconds:

13:30 = 13*60*60 + 30*60

That's what TYPO3 decided for and what extbase is now being aligned to.

Thanks anyway for you participation in this discussion, much appreciated, although we disagree.

Benjamin Franzke wrote in #note-51:

time in TYPO3 is stored as seconds since midnight of whatever day the time refers to.

And this is "wrong". If you don't like the term "wrong" then let it call "incomplete". No change to Extbase or Fluid can change that. Take this example:

Take a time field with an integer SQL field. An editor in Berlin enters "9:00". In the SQL field "9*3600" is stored. Now an editor in Japan opens up the record in the backend. He will see "9:00". Which is obviously wrong from his perspective, he will probably change it to "16:00", updating the SQL integer field to "16*3600" - ooops.
Example: https://3v4l.org/Fkghv
The FormEngine uses \gmdate() for integer times, instead of \date() (like it does for integer datetime or date).
See: https://github.com/TYPO3/typo3/blob/main/typo3/sysext/backend/Classes/Form/Element/DatetimeElement.php#L186
As long as this isn't unified (in FormEngine plus an Upgrade Wizard) no change of Extbase or Fluid can "solve" this consistently for everyone around the world.

The problem is that "seconds from midnight" (which I don't question at all here! It's fully OK!) also needs to respect the editor's client timezone when first persisting. But currently only date and datetime do this, not time.

Changing Extbase here will make it work for some people, yes, but will break it for other people. You just shift the problem around "on the world map". Because the original timezone information is still lost for time fields.

Just as with date/datetime fields, it should not matter for time fields where on earth you enter them.

A different valid approach/view would be: "seconds from midnight" using \gmdate() may not be converted into a \DateTime object, but merely a PHP string ("09:00") or an int (32400), leaving the handling fully to user-land code. This would be correct as well. But passing "seconds from midnight" without any orignal TZ info into \DateTime will always fail for people in different timezones (except the original one).

You either have to store times in the sense of \date() and pass it into \DateTime or store them in the sense of \gmdate() and echo them as string/int directly, leaving the handling to user-land. Mixing gmdate and DateTime is wrong incomplete.

Patch set 5 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/88836

Patch set 1 for branch 13.4 of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/88945

Patch set 6 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/88836

Patch set 2 for branch 13.4 of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/88945

Patch set 7 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/88836

Patch set 8 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/88836

Patch set 3 for branch 13.4 of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/88945

Patch set 9 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/88836

Patch set 4 for branch 13.4 of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/88945

Patch set 10 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/88836

Patch set 11 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/88836

Patch set 12 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/88836

Patch set 13 for branch main of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/88836

Patch set 5 for branch 13.4 of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/88945