Project

General

Profile

Actions

Feature #61110

open

Epic #77562: Misbehaviors with datetime values and timezones

Support for timezones in all date fields in TYPO3 BE

Added by Nikola Stojiljković almost 10 years ago. Updated over 2 years ago.

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

0%

Estimated time:
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

Files

be_user_date_settings.png (85.2 KB) be_user_date_settings.png Nikola Stojiljković, 2014-08-25 22:18
sample_fields_with_timezone.png (28.1 KB) sample_fields_with_timezone.png Nikola Stojiljković, 2014-08-25 22:18
sample_fields_with_timezone.png (14.7 KB) sample_fields_with_timezone.png Nikola Stojiljković, 2014-08-25 22:20
be_user_date_settings.png (48.5 KB) be_user_date_settings.png Nikola Stojiljković, 2014-08-25 22:20
sample_kolkata.png (10.6 KB) sample_kolkata.png Nikola Stojiljković, 2014-08-26 11:18
sample_belgrade.png (11.6 KB) sample_belgrade.png Nikola Stojiljković, 2014-08-26 11:18
sample_chatam.png (10.6 KB) sample_chatam.png Nikola Stojiljković, 2014-08-26 11:22

Related issues 21 (3 open18 closed)

Related to TYPO3 Core - Bug #27919: Preview in Admin Panel does not handle Timezone correctlyClosed2011-07-06

Actions
Related to TYPO3 Core - Feature #32081: document timezone handling / eval datetimeClosed2011-11-25

Actions
Related to TYPO3 Core - Bug #20088: In BE time and datetime fields GMT-0 will be shown by javascript, but GMT+1 will be savedRejectedErnesto Baschny2009-02-24

Actions
Related to TYPO3 Core - Bug #51918: Native date and datetime values do not consider timezoneClosed2013-09-11

Actions
Related to TYPO3 Core - Bug #21838: Date field on TCA can't accept 1-1-1970 asClosed2009-12-10

Actions
Related to TYPO3 Core - Bug #61952: bug dbType for date before 01-01-1970Closed2014-09-29

Actions
Related to TYPO3 Core - Bug #53640: js validation for datetime + required doesn't work for below 1970ClosedGeorg Ringer2013-11-14

Actions
Related to TYPO3 Core - Bug #37244: TCA date evaluation for dates lower 01-01-1970 failsClosed2012-05-17

Actions
Related to TYPO3 Core - Feature #64372: Add timezone-handling for value-display depending on FE-userClosed2015-01-20

Actions
Related to TYPO3 Core - Bug #64953: dbType date and datetime fields are saved in wrong timezoneClosed2015-02-10

Actions
Related to TYPO3 Core - Bug #21466: Deprecate serverTimezoneClosedOliver Hader2009-11-05

Actions
Related to TYPO3 Core - Task #59472: Backend date formatClosed2014-06-11

Actions
Related to TYPO3 Core - Bug #69290: Dates get reduced by a day if before 1970Closed2015-08-24

Actions
Related to TYPO3 Core - Bug #72878: wrong datetime handling, they are not UTC in dbClosed2016-01-21

Actions
Related to TYPO3 Core - Bug #102087: Backend user need a own timezoneClosed2023-10-04

Actions
Related to TYPO3 Core - Bug #92391: DateTime property has wrong TimeZone in Backend List viewClosed2020-09-23

Actions
Related to TYPO3 Core - Bug #101171: EXT:belog ignores timezone in filterNew2023-06-26

Actions
Related to TYPO3 Core - Bug #100462: Task execution time changed when timezone "Europe/Zurich" (or timezone with time change in spring)New2023-04-05

Actions
Related to TYPO3 Core - Bug #96890: Wrong processing and display of native datetime(dbType) fields due to server timezoneNewGabe Troyan2022-02-14

Actions
Related to TYPO3 Core - Task #104309: Use ISO dates in FormEngine date handlingResolved2024-07-04

Actions
Has duplicate TYPO3 Core - Feature #80559: datetime and time handling should always have timezone informationClosed2017-03-29

Actions
Actions #1

Updated by Nikola Stojiljković almost 10 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
Actions #2

Updated by Markus Klein almost 10 years ago

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

Actions #3

Updated by Nikola Stojiljković almost 10 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).

Actions #4

Updated by Markus Klein almost 10 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

Actions #5

Updated by Gerrit Code Review almost 10 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

Actions #6

Updated by Gerrit Code Review almost 10 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

Actions #7

Updated by Gerrit Code Review almost 10 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

Actions #8

Updated by Tymoteusz Motylewski almost 10 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.

Updated by Nikola Stojiljković almost 10 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).

Actions #11

Updated by Nikola Stojiljković almost 10 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...

Actions #12

Updated by Markus Klein almost 10 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)

Actions #13

Updated by Gerrit Code Review almost 10 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

Updated by Nikola Stojiljković almost 10 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:
Actions #16

Updated by Markus Klein almost 10 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.

Actions #17

Updated by Nikola Stojiljković almost 10 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.

Actions #18

Updated by Markus Klein almost 10 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).

Actions #19

Updated by Nikola Stojiljković almost 10 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.

Actions #20

Updated by Nikola Stojiljković almost 10 years ago

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

Actions #21

Updated by Tymoteusz Motylewski almost 10 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.

Actions #22

Updated by Markus Klein almost 10 years ago

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

Actions #23

Updated by Stefan Neufeind almost 10 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.

Actions #24

Updated by Gerrit Code Review over 9 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

Actions #25

Updated by Mathias Schreiber over 9 years ago

  • Target version changed from 7.0 to 7.1 (Cleanup)
Actions #26

Updated by Tymoteusz Motylewski over 9 years ago

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

Updated by Tymoteusz Motylewski over 9 years ago

  • Status changed from Under Review to In Progress
Actions #28

Updated by Gerrit Code Review over 9 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

Actions #29

Updated by Gerrit Code Review over 9 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

Actions #30

Updated by Gerrit Code Review over 9 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

Actions #31

Updated by Gerrit Code Review over 9 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

Actions #32

Updated by Benni Mack about 9 years ago

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

Updated by Susanne Moog almost 9 years ago

  • Target version changed from 7.4 (Backend) to 7.5
Actions #34

Updated by Anja Leichsenring almost 9 years ago

  • Sprint Focus changed from On Location Sprint to Remote Sprint
Actions #35

Updated by Tymoteusz Motylewski almost 9 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

Actions #36

Updated by Tymoteusz Motylewski almost 9 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/

Actions #37

Updated by Gerrit Code Review almost 9 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

Actions #38

Updated by Gerrit Code Review almost 9 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

Actions #39

Updated by Gerrit Code Review almost 9 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

Actions #40

Updated by Benni Mack almost 9 years ago

  • Target version changed from 7.5 to 7 LTS
Actions #41

Updated by Gerrit Code Review almost 9 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

Actions #42

Updated by Gerrit Code Review almost 9 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

Actions #43

Updated by Gerrit Code Review almost 9 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

Actions #44

Updated by Gerrit Code Review almost 9 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

Actions #45

Updated by Gerrit Code Review almost 9 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

Actions #46

Updated by Gerrit Code Review about 8 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

Actions #47

Updated by Gerrit Code Review about 8 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

Actions #48

Updated by Oliver Hader almost 8 years ago

  • Parent task set to #77562
Actions #49

Updated by Riccardo De Contardi about 7 years ago

  • Category set to FormEngine aka TCEforms
Actions #50

Updated by Markus Klein about 7 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.

Actions #51

Updated by Markus Klein about 7 years ago

  • Has duplicate Feature #80559: datetime and time handling should always have timezone information added
Actions #52

Updated by Benni Mack over 6 years ago

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

Updated by Susanne Moog over 6 years ago

  • Status changed from Accepted to Under Review
Actions #54

Updated by Gerrit Code Review over 6 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

Actions #55

Updated by Christian Kuhn almost 6 years 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.

Actions #56

Updated by Stefan Neufeind over 2 years ago

Stumbled across this again on a TYPO3 v10-system. DB correctly stores UTC, frontend correctly shows local time (according to phpTimeZone). But backend uses UTC ... which is a pain for editors to work with.

Since a lot of blockers from the past are done, for example we are now using moment.js, is having locale-aware support for the backend something we can take care of? Or are there further renovations pending for FormEngine or so that I'm not aware of, which need to be handled first before continuing this effort here?

Actions #57

Updated by Georg Ringer about 1 month ago

  • Related to Bug #102087: Backend user need a own timezone added
Actions #58

Updated by Georg Ringer about 1 month ago

  • Related to Bug #92391: DateTime property has wrong TimeZone in Backend List view added
Actions #59

Updated by Georg Ringer 10 days ago

  • Related to Bug #101171: EXT:belog ignores timezone in filter added
Actions #60

Updated by Georg Ringer 10 days ago

  • Related to Bug #100462: Task execution time changed when timezone "Europe/Zurich" (or timezone with time change in spring) added
Actions #61

Updated by Markus Klein 10 days ago

  • Related to Bug #96890: Wrong processing and display of native datetime(dbType) fields due to server timezone added
Actions #62

Updated by Markus Klein 9 days ago

  • Related to Task #104309: Use ISO dates in FormEngine date handling added
Actions

Also available in: Atom PDF