Feature #61110

Epic #77562: Misbehaviors with datetime values and timezones

Support for timezones in all date fields in TYPO3 BE

Added by Nikola Stojiljković about 5 years ago. Updated about 1 year ago.

Status:
New
Priority:
Should have
Assignee:
-
Category:
FormEngine aka TCEforms
Start date:
2014-08-21
Due date:
% Done:

0%

PHP Version:
Tags:
Complexity:
Sprint Focus:
Remote Sprint

Description

Johannes Feustel:

As an editor and as a dev
i want a documentation about how datetime filed acutally behave in typo3
so that i understand what values (TZ) to enter and how to test

AC
there should be a document/wiki page listing all relevant datetime fields like
  • access stop start of content and pages and records
  • admin panel
  • scheduler task
  • publish date for workspaces

Define meaningful use cases covering each of those like:
beeing in a specific timezone like CEST while server is in PST, entering a datetime into start field of a page
what will be save to the db? local time? server time?
when will it go live acutally?
How to test? what do i need to emter into the mock date filed of the admin panel (server time?)?

Description

All time fields in TYPO3 should be easy understandable (format) and lead to reliable result.
Format should ideally be based on a user setting and for all the places below TYPO3 should display the timezone

Following cases should be covered

  • admin panel
  • schedule for content desired for display in different timezone compared to server time
  • date time of a workspace to auto publish
  • parse datetime string possible like "today", "MM:HH DD-MM-YYYY", ISO 8601: "YYYY-MM-DD HH:MM", "Tue, 24 Nov 09 16:00:00 -0500" in the client?
  • make it possible for the editor to change format of datetime fields in user settings?
See also

be_user_date_settings.png View (85.2 KB) Nikola Stojiljković, 2014-08-25 22:18

sample_fields_with_timezone.png View (28.1 KB) Nikola Stojiljković, 2014-08-25 22:18

sample_fields_with_timezone.png View (14.7 KB) Nikola Stojiljković, 2014-08-25 22:20

be_user_date_settings.png View (48.5 KB) Nikola Stojiljković, 2014-08-25 22:20

sample_kolkata.png View (10.6 KB) Nikola Stojiljković, 2014-08-26 11:18

sample_belgrade.png View (11.6 KB) Nikola Stojiljković, 2014-08-26 11:18

sample_chatam.png View (10.6 KB) Nikola Stojiljković, 2014-08-26 11:22


Related issues

Related to TYPO3 Core - Bug #27919: Preview in Admin Panel does not handle Timezone correctly Closed 2011-07-06
Related to TYPO3 Core - Feature #32081: document timezone handling / eval datetime New 2011-11-25
Related to TYPO3 Core - Bug #20088: In BE time and datetime fields GMT-0 will be shown by javascript, but GMT+1 will be saved Rejected 2009-02-24
Related to TYPO3 Core - Bug #51918: Native date and datetime values do not consider timezone Closed 2013-09-11
Related to TYPO3 Core - Bug #21838: Date field on TCA can't accept 1-1-1970 as Closed 2009-12-10
Related to TYPO3 Core - Bug #61952: bug dbType for date before 01-01-1970 Closed 2014-09-29
Related to TYPO3 Core - Bug #53640: js validation for datetime + required doesn't work for below 1970 Closed 2013-11-14
Related to TYPO3 Core - Bug #37244: TCA date evaluation for dates lower 01-01-1970 fails Accepted 2012-05-17
Related to TYPO3 Core - Feature #64372: Add timezone-handling for value-display depending on FE-user New 2015-01-20
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 #21466: Deprecate serverTimezone Closed 2009-11-05
Related to TYPO3 Core - Task #59472: Backend date format Closed 2014-06-11
Related to TYPO3 Core - Bug #69290: Dates get reduced by a day if before 1970 Closed 2015-08-24
Related to TYPO3 Core - Bug #72878: wrong datetime handling, they are not UTC in db Closed 2016-01-21
Duplicated by TYPO3 Core - Feature #80559: datetime and time handling should always have timezone information Closed 2017-03-29

History

#1 Updated by Nikola Stojiljković about 5 years ago

So, the following will be done:
  • add "Date format" setting in the user settings
  • add "Date-time format" setting in the user settings
  • add "Time format" setting in the user settings
  • add "Time zone" setting in the user settings
  • add "Show timezone in date pickers" setting in the user settings
  • refactor jsfunc.evalfield.js so it can be easily extendable (and maintainable)
    • my idea is to go with Prototype.js OOP since it's already loaded and available
  • Integrate date.js parsing logic
  • add support for for overriding php.ini timezone setting in LocalConfiguration.php (since it's a bad practice to store dates in "server timezone") and document best use case scenarios. Additional useful info on storing dates in MySQL: http://cdn.oreillystatic.com/en/assets/1/event/36/Time%20Zones%20and%20MySQL%20Presentation.pdf

#2 Updated by Markus Klein about 5 years ago

Please refrain from creating anything new with Prototype. We try to get rid of it, please use jQuery.

#3 Updated by Nikola Stojiljković about 5 years ago

jQuery is not my first choice when working with "OOP" JS. And this refactoring has very little to do with DOM (apart from maybe using jQuery to read data attributes of a DOM element - a feature I will add for detail customization of the eval logic).

Is there an suggested alternative for the TYPO3 BE? I can always use something simple like this http://ejohn.org/blog/simple-javascript-inheritance/ (which is in fact based on Prototype.js).

#4 Updated by Markus Klein about 5 years ago

Hi!

Please discuss this on slack with others (I just Stucki to invite you), I'm not so much into the JS part.
Maybe talk to Andreas Wolf or Benni Mack.

Thanks

#5 Updated by Gerrit Code Review about 5 years ago

  • Status changed from New to Under Review

Patch set 1 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/32360

#6 Updated by Gerrit Code Review about 5 years ago

Patch set 2 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/32360

#7 Updated by Gerrit Code Review about 5 years ago

Patch set 3 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/32360

#8 Updated by Tymoteusz Motylewski about 5 years ago

Great contribution! Indeed problems with TZ were quite annoying.
I did not have time yet to go through the patch yet, but just wanted to confirm that with your patch dates and time should Always be stored in UTC, no metter what timezone user is in, and how server is configured.

#9 Updated by Nikola Stojiljković about 5 years ago

OK, so this is a big change, lets start with the concepts.

Currently, TYPO3 stores dates (and datetimes) from TCEforms using web server's PHP timezone setting. Quite extensive description is written here: https://forge.typo3.org/issues/32081.

This is wrong for a lot of reasons:
  • Using data on servers with different timezone will yield wrong results,
  • TCEforms is saving in server's timezone, while scheduler tasks are saved in UTC (which is correct way),
  • Examine the following case: server's system timezone is America/Los_Angeles, php.ini timezone is Europe/Berlin. Calling PHP time() function will return current timestamp in UTC timezone.

So in order to not break the existing installations there's a new setting $GLOBALS['TYPO3_CONF_VARS']['SYS']['storeDatesInUTC'] which defaults to FALSE. It is recommended to set this to TRUE on all new installations.

There's a new tab in the user settings:

Should be self-explanatory. The default setting is to leave the timezone to the client's browser (which will fallback to OS settings).

If you select to show timezone, it will be displayed by default as UTC+XX:00. The setting respects the daylight savings time, so this is for example correct display for CET/CEST:

Behind the scenes, there was a huge rewrite of the jsfunc.evalfield.js. I simply couldn't read through the old code. I wanted to use prototype.js OOP setup, but since there's an intention to drop prototype.js, I introduced similar Class object (with support for defining true JavaScript singletons). The new FieldEvaluation.Utility is what used to be evalFunc, while evalFunc_dummy is now FieldEvaluation.EvaluationSettings. Most of the transformations are actually copy'n'paste. Except for the alpha/alphanum* evaluations which are now done using RegExp and date/datetime fields which are now using moment.js.

The part which uses moment.js assume that moment.js is already loaded using require.js (which will not be the case on the first execution of evalFunc). I opted not to go too much in refactoring here, but could do it if needed... The changed tceforms.js loads rsvp.js and moment.js and formats the dates according to user settings. It expects that the user settings are already in JS memory (see BackendUserAuthentication ->getDateSettings). If not, it will load them using ExtDirect. So there might be a short period of time when the date field will have blank value (noticable in IE8 only from my tests).

#11 Updated by Nikola Stojiljković about 5 years ago

One more note: date.js has better support in parsing natural language date strings, but moment.js plays much nicer with timezones, so opted for it instead. That's why I added date input field placeholders. Moment.js will parse standard (and ISO) date formats as well as a fallback...

Anyway, if you guys and gals feel like we need natural language processing, we can add date.js as well :) My impression is that it would be too much...

#12 Updated by Markus Klein about 5 years ago

Thanks for your awesome work. I will try to read through the code asap.

One question: I assume the lib supports half hour and quarter hour timezones as well, but does it support DST for all variants that are out there in the wild?
(I guess you know that funny video about time(-zone) programming)

#13 Updated by Gerrit Code Review about 5 years ago

Patch set 4 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/32360

#14 Updated by Nikola Stojiljković about 5 years ago

Markus Klein wrote:

Thanks for your awesome work. I will try to read through the code asap.

One question: I assume the lib supports half hour and quarter hour timezones as well, but does it support DST for all variants that are out there in the wild?
(I guess you know that funny video about time(-zone) programming)

Yes, here are the screenshots of the same fields:
  • Europe/Belgrade timezone
  • Asia/Kolkata timezone
  • Pacific/Chatham:

#16 Updated by Markus Klein about 5 years ago

As I said, I assumed that, but the question is about daylight saving time.
"most" countries have some certain dates where the clock is changed, but there is an awefull list of exceptions.

Check that video:
http://www.youtube.com/watch?v=-5wpm-gesOY

So maybe we should do the DST setting as a manual setting for the user.

#17 Updated by Nikola Stojiljković about 5 years ago

Markus Klein wrote:

As I said, I assumed that, but the question is about daylight saving time.
"most" countries have some certain dates where the clock is changed, but there is an awefull list of exceptions.

Check that video:
http://www.youtube.com/watch?v=-5wpm-gesOY

So maybe we should do the DST setting as a manual setting for the user.

If you select a timezone (based on the usual list), it already defines if DST is in place or not and when (as you can see in the sample above for CET/CEST in Europe/Belgrade timezone). Adding a DST setting will just confuse a regular BE user. Furthermore, moment.js doesn't have a documented way of retrieving DST dates per timezone and I would refrain from hacking through it in order to allow easier upgrades in future.

#18 Updated by Markus Klein about 5 years ago

Ok, I'm confused now.

How does moment.js implement DST?
According to you screenshots it deducts the DST from the timezone, but that is plain wrong. (as explained in the video, DST switching highly depends on the country/location)

I agree we shouldn't hack that on our own, I propose to disable DST handling completely (if that is possible).

#19 Updated by Nikola Stojiljković about 5 years ago

Markus Klein wrote:

Ok, I'm confused now.

How does moment.js implement DST?
According to you screenshots it deducts the DST from the timezone, but that is plain wrong. (as explained in the video, DST switching highly depends on the country/location)

Yes, and selecting a timezone means selecting a location. Please take a look at the new user BE settings. This list is used: http://php.net/manual/en/timezones.php.

I agree we shouldn't hack that on our own, I propose to disable DST handling completely (if that is possible).

Moment.js automatically handles DST, the same way as PHP does.

There might be some confusion regarding this. Selecting a timezone means selecting a location - that defines DST start/end dates per year. When displaying a timezone in the field, you really don't need to see the location but time zone in format (UTC+XX:XX). Moment.js even thrown out the support for displaying timezone in the short format (like CET, CEST, PDT,...) since the browsers do not implement it consistently.

#20 Updated by Nikola Stojiljković about 5 years ago

One more note, I included the moment.js timezone library with data for all years (http://momentjs.com/timezone/).

#21 Updated by Tymoteusz Motylewski about 5 years ago

So in order to not break the existing installations there's a new setting $GLOBALS['TYPO3_CONF_VARS']['SYS']['storeDatesInUTC'] which defaults to FALSE. It is recommended to set this to TRUE on all new installations.

Can't we make it correct (UTC) by default? Especially as there are already inconsistencies in the core. This patch is targeting 6.3 anyway. In the separate patchset we could add a migration wizard to recompute the dates when sb is updating the installation.
I'm opting in for storing dates in UTC, as most people especially newcommers will forget to change this setting.

#22 Updated by Markus Klein about 5 years ago

I think the patch already has this by default for new installations.

#23 Updated by Stefan Neufeind about 5 years ago

Just a side-note: When we have it "correct" in TYPO3-core, let's then look what Extbase stores and reads. I just had a nightmare with Extbase not storing DateTime-objects correctly - and when we have a defined way how to store it in DB, we can look at that part as well.

#24 Updated by Gerrit Code Review almost 5 years ago

Patch set 5 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/32360

#25 Updated by Mathias Schreiber almost 5 years ago

  • Target version changed from 7.0 to 7.1 (Cleanup)

#26 Updated by Tymoteusz Motylewski almost 5 years ago

  • Assignee changed from Nikola Stojiljković to Tymoteusz Motylewski
  • Sprint Focus set to On Location Sprint

#27 Updated by Tymoteusz Motylewski almost 5 years ago

  • Status changed from Under Review to In Progress

#28 Updated by Gerrit Code Review almost 5 years ago

  • Status changed from In Progress to Under Review

Patch set 6 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/32360

#29 Updated by Gerrit Code Review almost 5 years ago

Patch set 7 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/32360

#30 Updated by Gerrit Code Review almost 5 years ago

Patch set 8 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/32360

#31 Updated by Gerrit Code Review almost 5 years ago

Patch set 9 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/32360

#32 Updated by Benni Mack over 4 years ago

  • Target version changed from 7.1 (Cleanup) to 7.4 (Backend)

#33 Updated by Susanne Moog over 4 years ago

  • Target version changed from 7.4 (Backend) to 7.5

#34 Updated by Anja Leichsenring over 4 years ago

  • Sprint Focus changed from On Location Sprint to Remote Sprint

#35 Updated by Tymoteusz Motylewski about 4 years ago

  • Assignee deleted (Tymoteusz Motylewski)

2 good news:
- with forengine refactoring, and removal of jsfunc.evalfield.js implementation of this feature will be easier and cleaner
- the last blocker (timezone support in datetimepicker) is now also gone.
see the development branch (should be released soon as v5)
https://github.com/Eonasdan/bootstrap-datetimepicker/commits/development

#36 Updated by Tymoteusz Motylewski about 4 years ago

even better:
timeozne support was added to the stable branch of date picker 4.17.37
see http://eonasdan.github.io/bootstrap-datetimepicker/Changelog/

#37 Updated by Gerrit Code Review about 4 years ago

Patch set 10 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/32360

#38 Updated by Gerrit Code Review about 4 years ago

Patch set 11 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/32360

#39 Updated by Gerrit Code Review about 4 years ago

Patch set 12 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/32360

#40 Updated by Benni Mack about 4 years ago

  • Target version changed from 7.5 to 7 LTS

#41 Updated by Gerrit Code Review about 4 years ago

Patch set 13 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/32360

#42 Updated by Gerrit Code Review about 4 years ago

Patch set 14 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/32360

#43 Updated by Gerrit Code Review about 4 years ago

Patch set 15 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/32360

#44 Updated by Gerrit Code Review about 4 years ago

Patch set 16 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/32360

#45 Updated by Gerrit Code Review about 4 years ago

Patch set 17 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at http://review.typo3.org/32360

#46 Updated by Gerrit Code Review over 3 years ago

Patch set 18 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/32360

#47 Updated by Gerrit Code Review over 3 years ago

Patch set 19 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/32360

#48 Updated by Oliver Hader about 3 years ago

  • Parent task set to #77562

#49 Updated by Riccardo De Contardi over 2 years ago

  • Category set to FormEngine aka TCEforms

#50 Updated by Markus Klein over 2 years ago

  • Status changed from Under Review to On Hold
  • Target version deleted (7 LTS)

This needs to be part of TYPO3 9 as v8 did some first steps into the right direction.

#51 Updated by Markus Klein over 2 years ago

  • Duplicated by Feature #80559: datetime and time handling should always have timezone information added

#52 Updated by Benni Mack about 2 years ago

  • Status changed from On Hold to Accepted
  • Target version set to Candidate for Major Version

#53 Updated by Susanne Moog about 2 years ago

  • Status changed from Accepted to Under Review

#54 Updated by Gerrit Code Review almost 2 years ago

Patch set 20 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/32360

#55 Updated by Christian Kuhn about 1 year ago

  • Status changed from Under Review to New

resetting this feature back to 'new' since the patch again stalled for a long time in the review system. please feel free to resurrect it if work continues.

Also available in: Atom PDF