TYPO3 Core

Some infos to kick start this documentation:

The form presented to the client by TCEmain converts the "Human readable" date as entered in the input field (which can be a date, or a string like "d" or "d+1" for tomorrow) into a UTC based timestamp, which is what is send to the server (for process_datamap).

Conversion of client's strings like "d", "d+1" etc is done by t3lib/jsfunc.evalfield.js method evalFunc_input().

Conversion of the client date+time into UTC is done by t3lib/jsfunc.evalfield.js method convertClientTimestampToUTC().

TCEmain will then transform the UTC based unixtime into server timezone based unixtime (a timestamp which represents the same date+time but in server timezone).

For example:

1. Input from user is a date+time "2011-11-29 00:00:00".
2. Client will transform this input into the unixtime "1322524800" (represents "2011-11-29 00:00:00" in UTC) and send that to the server for processing by TCEmain
3. TCEmain will transform this UTC unixtime into a unixtime which represents the same date+time information in server's timezone (so it has to shift the unixtime). Imagine server timezone is UTC+1. Unix timestamp "1322521200" represents "2011-11-29 00:00:00" in UTC+1. So this is stored in the database.

Note that to go from a UTC timestamp to a server timestamp representing the same date+time, this transformation is done in t3lib_TCEmain::checkValue_input_Eval():

$value -= date('Z', $value);

date('Z') is the timezone offset in seconds. The offset for timezones west of UTC is negative, and for those east of UTC is positive. So date('Z') for UTC+1 is +3600.

For the above example:

1322524800 - 3600 = 1322521200

Notes about unixtime and timezones:¶

"1322524800" is a timestamp and has no timezone information attached to it. So depending on which timezone you are refering to this unix time, a different date+time is interpreted:

...
1322524800 = 2011-11-28 23:00:00 UTC-1
1322524800 = 2011-11-29 00:00:00 UTC
1322524800 = 2011-11-29 01:00:00 UTC+1
...

Note about the rationale of not using UTC in the database:¶

The background of storing unixtime based on server timezone in the database instead of storing them UTC based is "backwards compatibility": TYPO3 has always stored the timestamps based on server TZ (and not UTC based), which allows all PHP and TypoScript views to use the "strftime" and similar for formatting the output (instead of using gmstrftime).

What if input doesn't come from "the user"¶

If you are doing your own TCEmain processing (in your PHP extension, for example when importing the information from another source), you might be irritated by the transformation mentioned in step 3 above. To avoid this transformation, instruct TCEmain not to process any transformations:

$tce = t3lib_div::makeInstance('t3lib_TCEmain');
$tce->stripslashes_values = 0;
$tce->debug = 0;
$tce->dontProcessTransformations = 1;
$tce->start(array(),$cmdmapArray);
$tce->process_cmdmap();

Ernesto, thanks a lot for the detailed explanations. I guess I'll fit them in an appendix as they are quite long (but worthy!).

Just tested it with 6.2 and it does not work like it's explained above.
In short, it seems that TYPO3 is already storing dates in UTC.

In my understanding the line
$value -= date('Z', $value);

removes the server timezone offset from the date, making it UTC.

@timek:

Substracting date(Z) from a unixtime interpreted as UTC transforms it into the same datetime interpreted in your timestamp (i.e. ). I.e.:

date(Z) from UTC+0100 = +3600

1322524800 = 2011-11-29 00:00:00 UTC
1322524800 - 3600 = 2011-11-28 23:00:00 UTC = 2011-11-29 00:00:00 UTC+0100

I know, this requires some mind twisting... or maybe drawing some time-lines on a piece of paper. :)

If you see UTC dates stored in your database, it means your server is configured for UTC. Many "cloud based" VMs and regular distributions default to that nowadays. Just check with "date" in the Shell

The "timezone offset problem" in TYPO3 was always only a problem if the server timezone was not UTC. These bugs were fixed way back in some long session at the developer days back in 2007 (https://git.typo3.org/Packages/TYPO3.CMS.git/commit/32a890cadc057f29f0886ff71d2b66031a69eec8) which resulted in the algorithm explained above. Up until 6.2 this wasn't touched, and I don't think it was changed afterwards (but I might be wrong, as I haven't followed closely the 7-branch development).

That still means the stored result is UTC as it stands for "2011-11-28 23:00:00 UTC"
The UTC+0100 is just that the interpretation AFTER a timezone interpretation.

which will be correct again if interpreted int UTC+1, not in UTC. If you interpret that timestamp in UTC it stays "2011-11-28 23:00:00 UTC"
Therefore the end result of the timestamp stored in the database is UTC.
Also I could be wrong but a timestamp itself actually is always UTC, just the representation might differ.

It only becomes "2011-11-29 00:00:00 UTC+0100" once you parse it in the server timezone which happens by default with things like DateTime etc.
But if you set the Timezone of DateTime to UTC afterwards you would get "2011-11-28 23:00:00" as output.

As the server is most likely in your local timezone the endresult would be UTC. Or if you support timezones per Backend-User, if the Backend-User is set to the server timezone.

Of course, if the user really inputed UTC then your assumption would be correct.
But honestly it is quite unlikely as a user will input the locally wanted time and not a UTC time.
And it is far more likely the server is set to the user's local timezone than some random timezone.

Of course, this is different if the same installation is used upon multiple timezones but as TYPO3 doesn't support a user timezone setting yet, it will be stored in the server timezone by the user which most likely will be UTC then and if not it would still be the server timezone.
Therefore, the timestamp stored in the database is actually a correct UTC interpreation of the wanted timestamp.

However, as the main point is because of a feature regarding timezones and BE-User settings:
So if we want to implement timezone settings for the BE-User the transformation itself has to be removed because that would be handled correctly via JavaScript or modified to use the difference between UTC and the expected timezone.
For instance, with the example above if the user sets the timezone to UTC then they would see "2011-11-28 23:00:00" which is correct if we expect the user that inputed the date to have the same timezone as the server which would be UTC+0100 because if the user sets the timezone to UTC+0100 then it would be shown as "2011-11-29 00:00:00".
Therefore, we just have to ensure that the default timezone for the BE-user is the same as the server. That way the output in the Backend will stay the same and the FE output will also stay the same when the user stores new values.
Even if that timezone might be wrong for where the BE-user lives.
However, that will be something that requires additional handling anyway and will not be fixed by keeping the timezone modifications.

The only thing that might make sense is a possibility to easily convert all values form one specific timezone to UTC if the user and the server timezone do indeed mismatch but that won't be solved by doing the modification if a certain setting is set because it would mean the user would have to set the timezone always to UTC to ensure they see what they saw before which is very likely wrong.
But I serisouly do not think we need it.

However, adding a setting for FE to easily modifiy the timezone, e.g. config.timezone in TypoScript and then using set_default_timezone would basically also be a requirement when adding BE-user timezones. That way one can set the correct timezone depending on e.g. sys_language_uid values

Short answer: No. :)

Unixtime has no timezone information attached to it, and it represents a different time of the day depending on the timezone you are talking about:

unixtime "0" ZERO = 1970-01-01 00:00:00 in UTC = 1970-01-01 01:00:00 in GMT+0100 = 1969-12-31 23:00:00 in GMT -0100 = ....

We store the unixtime that represents the date+time the user inputted in the timezone of the server. That's it. I didn't really understood what you were trying to explain, but I feel that you are wrong. :)

The user-input currently in the backend is only a "date and a time" and we don't know (or don't care) what timezone he is talking about. We just want to make sure when printing the date+time out again or when considering the "hidden before" or "after" that we do the "right thing".

If we want the backend and the datetime fields to be "timezone aware" we should stop using unixtimes for that, because that's not their intended use! Let's make real DateTime with Timezone representations in the database instead and do it completely new. No "shifting unixtime" around anymore, this is just ugly and error prone!

Well, I agree with you there (regarding time representations).
In any case I think we might have "passed" each other in the argument. And I got too complicated in explaining what I meant.
My fault.

But basically we don't know in what we store the time, we just intend to ensure Frontend und Backend are currently the same and which case you are right we need to do the shift.
But as we don't know which timezone the user is in we cannot say if it is stored in UTC or whatever.
We could only say that if the server timezone and the usertimezone are the same it is stored in UTC.

And yes I meant that regarding representation actually. Unix timestamp basically is a time difference stored based on 1970-01-01 00:00:00 in UTC.
Actually, the others are only representations of the same timestamp.

Also I agree that storing the datetime information as a datetime field would be the best solution.
However, in order to avoid breaking changes we would need additional datetime/time etc. eval values because otherwise the storage will not work and the representation too.
Otherwise all extensions that use the old format would be breaking and as many use at least starttime/endtime that's basically all :)

But maybe we could provide a "compatbility layer" instead or an easy upgrade wizard that finds all date/datetime fields in TCA and converts them.

no compatibility layer.. just deprecate the old way of doing it and introduce a new better way. And provide "migrations" and instructions on how to adapt to the new fields, and make the new way heavily documented! :)

Also a possibility. Actually I like that one better, definitely migration options are required but it could be realtively automated because we can detect the old fields.

migration of data is not a problem, but the PHP code that uses the data needs to be "adapted" and this cannot be automated. For extensions authors we need then some instructions on how to work with the new kind of fields.

There is already "something like that" in TCA since 6.0:

https://docs.typo3.org/typo3cms/TCAReference/Reference/Columns/Input/Index.html#dbtype

I haven't used it yet but you might want to explore how "good" it already is. The docs doesn't mention anything about timezones...

Interesting regarding dbType. It isn't used anywhere in core yet though :)

At least extbase DateTime conversion should be supported for this field by default.

Hi,
Thanks Ernesto for stepping in :) You're right of course.
I've started a document/blueprint to get the current status and describe the way to improve it.
Please add your 2c if you have a moment.
https://docs.google.com/document/d/1IWH3MtI8sT4JPXDnkQuCPGTLjE9uafcuxXci9XdroLg/edit

@Tymoteusz:
From what I could gather after various tests on 6.2 with the above mentioned jsfunc.eval.js and convertClientTimestampToUTC
The timestamp that is sent to convertClientTimestampToUTC is actually already in UTC because it uses getUTCHours, ... methods.

However, convertClientTimestampToUTC results in the the timestamp that was inputed to being sent as timestamp like it were inputed in UTC by the user and not in the client timezone.
Because the method uses "- dateObj.getTimezoneOffset()" and JavaScript returns a negative value here.
I guess that might be the intended result, however, as we don't support timezones anywhere currently and therefore this conversion is necessary to output the timestamp correctly again after saving.

Uff, nice to see so old issues being closed! Good read and brings me back some memories!