Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP

Loading…

Support for editing the site timezone #5476

Closed
mattab opened this Issue · 32 comments

3 participants

Matthieu Aubry Anthon Pang Anonymous Piwik user
Matthieu Aubry
Owner

A server can be located in the US but the website being used by people from WEST EUROPE. Piwik user wants to see his reports in the WEST EUROPE timezone.

  1. UI changes
add a new setting Timezone in the admin UI the timezone is website based – each website can have a different timezone set. All users looking at stats for a given website will see the data in the website’ specified timezone. (optional) a “default timezone” can be set by the Super User, that would be used to define the timezone by default when creating new websites. When creating new websites, the timezone dropdown would be pre-selected with this “default timezone” but users could still change it. If the Super User doesn’t define this default timezone, it falls back to the server timezone (current behavior).
  1. API changes
timezone being an attribute of the website, the SitesManager API would have to slightly updated to reflect the new parameter. We should keep backward compatibility if possible. The timezone wuold for example become the last parameter of``` addSite( $siteName, $urls, $timezone ) ```} and would be optional. If not specified, the timezone would default to the general “default timezone” (see above).
  1. Tracking
all dates and times must be stored in the DB in UTC. This means that in the various log_* tables, any field that is DATE or DATETIME should be set with dates and datetimes that are based in UTC. currently in the Archiving process, a standard archiving query looks like: ``` SELECT name, type, count(distinct t1.idvisit) as nb_visits, count(distinct visitor_idcookie) as nb_uniq_visitors, count(*) as nb_hits FROM (“.$archiveProcessing→logTable.” as t1 LEFT JOIN “.$archiveProcessing→logVisitActionTable.” as t2 USING (idvisit)) LEFT JOIN “.$archiveProcessing→logActionTable.” as t3 USING (idaction) WHERE visit_server_date = ? AND idsite = ? GROUP BY t3.idaction ORDER BY nb_hits DESC ```

You can see that the WHERE is currently on the visit_server_date field which is a DATE only, which is a problem for selecting records that are in a given timezone.

  • for queries working on the log_visit table, the WHERE should probably be on visit_first_action_time instead. The INDEX on the log_visit table should be updated to include visit_first_action_time instead of visit_server_date.
  • for queries working on the log_conversion table, the WHERE should probably be on server_time instead of visit_server_date. The INDEX on the log_conversion table should also be updated to include this field.
  • I believe mysql is clever and is able to use the INDEX on a DATETIME field for a range query. We should double check that this is the case :)
  1. Archiving
After these changes to the tracking tables, it will be possible to update the few queries doing the archiving and instead of using visit_server_date, make them use the right UTC time fields, and select records that are between the website specified timezone. For example, the selected date (June 25 2009) to the website timezone: ```

visit_server_date = ‘2009-06-25’ ``` would become for example ``` visit_first_action_time >= ‘2009-06-24 16:00:00’ AND visit_first_action_time <= ‘2009-06-24 15:59:59’ ```

  • this conversion of the date object would probably be done around Archive::build() – and the few archiving queries would have to be updated
  1. Other notes
when changing the timezone setting for a given website, all previous stats are not re-processed and will still show in the previously selected timezone. The new timezone is applied only to future reports. Note: this is how a lot of software work (including GA, adwords, etc.)
Matthieu Aubry
Owner

A better solution would be to store all dates/time in GMT, and process the dates relative to the “Local time” set by the Piwik Super User.

Anthon Pang
Collaborator

From #479, a timezone configuration mismatch between php and mysql can cause a lag in dashboard reporting. Proposed change in comment 2 should fix this problem.

Matthieu Aubry
Owner

potential thing to fix in the code: all timestamp may have to be Mysql-based rather than php-based (or the opposite, to spec) in case the webserver and mysql server are on different times/timezones.

For example, in core/ArchiveProcessing.php line 250:

``` $this→maxTimestampArchive = time() – Zend_Registry::get(‘config’)→General→time_before_archive_considered_outdated; ```

  • review all existing timestamps coming in and out of the database and ensure they are consistent
Anthon Pang
Collaborator

(In [1959]) refs #5476 - handle timezone mismatch more gracefully

Matthieu Aubry
Owner

Some decisions

- set data to UTC for all pre 5.2 php versions. This would be a regression for them, as currently some might have set their php timezones to their location and have data in their timezones. However, I don't want to write code too complicated and I prefer to boolean approach UTC (timezone php not available) VS specified timezone (for >= 5.2)
- users with timezone functionnality will be able to select, per website, the timezone in which the reports will be displayed. Timezone change will only apply to reports going forward.

The plan is to:
- remove all mysql NOW(), CURDATE(), etc. and only use php generated dates
- use UTC for attributes like: site.ts_created, user.date_added, etc.
- use UTC for all tracking data in log_* tables. currently, data is recorded using the specified timezone in php.ini (if none, php defaults to UTC)
- use website specified timezone for archive_* data
- deprecate the php/mysql timezone mismatch in the system requirement page and remove the code to fetch timezones from the db adapters
- add timezone detection in system requirements, as an "optional" feature
Matthieu Aubry
Owner

feedback from Anthon

  • installation check for old timezone databases (e.g., 5.3.2 uses version 2010.3)
  • add a FAQ where to download the latest version: http://pecl.php.net/package/timezonedb (2010.5)
  • document the DST issues:
    • plus/minus an hour on cutover days
    • DST cutover may not take effect on the correct day if using an obsolete timezone database
Matthieu Aubry
Owner

(In [2006]) Refs #5476

  • Added timezone setting per website. Added API to fetch the list of supported timezones. UTC Manual offsets are supported even if the PHP doesn't have timezone support built in.
  • Added default timezone setting that the Super user can set. It will be used to pre-select timezones when creating new websites, or setting the default timezone when adding websites via the API without specifying the timezone.
  • Default timezone set to UTC to all existing websites on update. PHP Default timezone set to UTC in index.php and piwik.php.
  • Removed all usage of mysql date/time functions (which are internally doing timezone conversions based on mysql timezone), now using php generated timestamps (in UTC), or dates manually converted to the website timezone.
  • Altered the few Mysql fields that were defaulting to "Current timestamp", they now default to NULL.
  • Deprecated the field log_visit.server_date and log_conversion.server_date as they are now not used. Must use DATE(visit_last_action_time) instead. Note that the new INDEX on (idsite, visit_last_action_time, config_md5config) will greatly benefit the Live! plugin.
  • Deprecated Piwik_Date->get, must now use Piwik_Date->toString
  • Deprecated the DB adapters getCurrentTimezone() feature, as we now don't rely on the DB timezone. Also removed the warning from the install screen.
Matthieu Aubry
Owner

To do in this ticket

Regressions to fix:

  • Purge of temporary archives is now not working. It was relying on the fact that mysql and php timezones were the same. See protected function postCompute() in Period.php
  • The new timezone functionnality and deletion of the log_visit.visit_server_date breaks the existing Archiving plugin (GeoIP, SearchEnginePosition), these plugins (and others?) must be updated to the latest version.
  • check anonymous user created with right timestamp Piwik_Date::now()->getDatetime()
  • getTimezonesList() API is not compatible with API response auto formatting

More testing

  • check that prepareArchive() tests for past and future dates work as expected (unit tests)
  • add unit test DST change in ArchiveProcessing
  • the calendar should show dates in the selected website timezone. If the website is UTC-10, when loading Piwik on Friday at 8AM UTC, Today in the calendar should be Thursday
  • test RSS link on offset date works+ test pubDate correct

Also, code review and more testing very appreciated :) this is rather large change and I'm sure there are still some bugs.

Matthieu Aubry
Owner

Also

  • Add timezone global setting in the install, in the super user account creation screen
Matthieu Aubry
Owner

also

  • disable GeoIp, SearchEnginePosition on update, as it breaks the plugins. Put a message asking user to upgrade.
Matthieu Aubry
Owner
Matthieu Aubry
Owner
  • update http://piwik.org/docs/setup-auto-archiving/ to specify that the archiving should be executed every hour, rather than every day, because websites in different timezones will trigger archiving at different hours
Anonymous Piwik user

Hi, I have made an update to the latest svn. After update the Live plugin shows me the incorrect UTC. I have set the correct timezone global and on site unfortunately without any changes. Is it just me, or is it a little bug? cheers

Matthieu Aubry
Owner

(In [2058]) Refs #5476

  • Adding timezone during piwik install. Will set default timezone, and use it for the first created website.
  • Had to apply manual patch to QuickForm Select to handle optgroups, added unit tests to check patch is applied
  • Skips the Database check screen if there was no error
Anthon Pang
Collaborator

(In [2066]) refs #5476 - sort cities within each continent

Matthieu Aubry
Owner

(In [2067]) Refs #5476

  • Calendar for a given website will show dates relative to this websites's timezone
  • API results now display relative to website's timezone
  • MultiSites will convert "today" and "yesterday" to Piwik default timezone
  • MultiSites calendar min and max date are the min and max date based on website's timezones. For example, the max date might be tomorrow in UTC if some websites are set to UTC+12
Matthieu Aubry
Owner
  • Visits by server time should report time in website timezone rather than UTC
Matthieu Aubry
Owner
  • Time displayed in Live! widget should be in the website timezone
Matthieu Aubry
Owner

(In [2078]) Refs #5476

  • Deleting temporary daily / weekly / monthly archives once a day
  • added a few debug messages in archiveProcessing
  • disabling screen logger by default, as it is used to print debug statements. It can be enabled in config.ini.php by piwik developers.
Matthieu Aubry
Owner

(In [2079]) Refs #5476

  • Live! widget now displays dates and times relative to the website timezone
  • cleaned up code a bit
  • removed ability to not specify the idSite in Live! API requests. It is now mandatory (feature was not used and doesn't really makes sense)
Matthieu Aubry
Owner

(In [2081]) Refs #5476

  • disables GeoIP and SearchEnginePosition during 0.6 upgrade, pointing users to the download page of the plugins so they can manually upgrade
Matthieu Aubry
Owner

(In [2082]) Refs #5476

Anthon Pang
Collaborator

(In [refs #5476 / 2058) - changed webtest to expect database check to be skipped

Anthon Pang
Collaborator

(In [refs #5476 / 2058) - add timezone selection to webtest

Anthon Pang
Collaborator

New installation of 0.6-rc1. Visiting the Dashboard for the first time (no visits yet), I see the following errors in the widgets.

In the Last Visits Graph:

Open Flash Chart

JSON Parse Error [Syntax Error]
Error at character 0, line 1:

0: Preparing archive:  skipped, archive is before the website was created.<br />Preparing archive:  skipped, archive is before the website was created.<br />Preparing archive:  skipped, archive is before the website was created.<br />Preparing archive:  skipped, archive is before the website was created.<br />Preparing archive:  skipped, archive is before the website was created.<br />Preparing archive:  skipped, archive is before the website was created.<br />Preparing archive:  skipped, archive is before the website was created.<br />Preparing archive:  skipped, archive is before the website was created.<br />Preparing archive:  skipped, archive is before the website was created.<br />Preparing archive:  skipped, archive is before the website was created.<br />Preparing archive:  skipped, archive is before the website was created.<br />Preparing archive:  skipped, archive is before the website was created.<br />Preparing archive:  skipped, archive is before the website was created.<br />Preparing archive:  skipped, archive is before the website was created.<br />Preparing archive:  skipped, archive is before the website was created.<br />Preparing archive:  skipped, archive is before the website was created.<br />Preparing archive:  skipped, archive is before the website was created.<br />Preparing archive:  skipped, archive is before the website was created.<br />Preparing archive:  skipped, archive is before the website was created.<br />Preparing archive:  skipped, archive is before the website was created.<br />Preparing archive:  skipped, archive is before the website was created.<br />Preparing archive:  skipped, archive is before the website was created.<br />Preparing archive:  skipped, archive is before the website was created.<br />Preparing archive:  skipped, archive is before the website was created.<br />Preparing archive:  skipped, archive is before the website was created.<br />Preparing archive:  skipped, archive is before the website was created.<br />Preparing archive:  skipped, archive is before the website was created.<br />Preparing archive:  skipped, archive is before the website was created.<br />Preparing archive:  not archived yet, starting processing...<br />Processing archive 'day',                              idsite = 1 (definitive archive) -                               UTC datetime [2010-04-11 04:00:00 -> 2010-04-12 03:59:59 ]...<br />Preparing archive:  not archived yet, starting processing...<br />Processing archive 'day',                              idsite = 1 (temporary archive) -                                UTC datetime [2010-04-12 04:00:00 -> 2010-04-13 03:59:59 ]...<br />{  "elements": [      ],  "num_decimals": 0,  "is_fixed_num_decimals_forced": true,  "is_decimal_separator_comma": false,  "is_thousand_separator_disabled": false,  "x_axis": {    "colour": "#596171",    "grid-colour": "#E0E1E4",    "steps": 7,    "labels": {      "size": 11,      "labels": [        "Sun 14 Mar",        "",        "",        "",        "",        "",        "",        "Sun 21 Mar",        "",        "",        "",        "",        "",        "",        "Sun 28 Mar",        "",        "",        "",        "",        "",        "",        "Sun 4 Apr",        "",        "",        "",        "",        "",        "",        "Sun 11 Apr",        ""      ],      "steps": 2    }  },  "y_axis": {    "colour": "#ffffff",    "grid-colour": "#E0E1E4",    "min": 0,    "max": 1,    "steps": 0  },  "tooltip": {    "shadow": true,    "stroke": 1  },  "bg_colour": "#ffffff"

In other widgets:

Preparing archive:  archive already processed [id = 2]...

My timezone is America/Toronto (currently UTC-4). The current time was 17:12 PM (EDT). ts_archived on the blobs was 2010-04-10 21:12:55.

Anthon Pang
Collaborator

Scratch my last comment. My test env ignored the logging changes in [2078].

Matthieu Aubry
Owner

(In [2091]) Refs #5476

  • reverted field log_visit.visit_server_date and the INDEX on this field, as it is used by Tracker_Visit->recognizeTheVisitor() and significantly breaks performance if removed. The index on visit_last_action_time that was used instead has a very high cardinality, and queries were very slow, locking up the whole table. Drawback is that we now have 2 distinct large indexes on the largest Piwik table...
  • Fixed issue from forum where archiving for today failed to execute in the crontab http://forum.piwik.org/index.php?showtopic=7211
  • Fixed warning in Piwik_Tracker_Db->fetchOne causing tracking to fail on empty cache
Matthieu Aubry
Owner

(In [2095]) Refs #5476 fix typo

Matthieu Aubry
Owner

(In [2124]) Refs #5476 regression admin users can access Websites tab

Matthieu Aubry
Owner

(In [2126]) Refs #5476 Fixing the Visits by server time report to report times in the website's timezone

Matthieu Aubry
Owner

Closing ticket as all outstanding issues are now fixed. Please open new tickets for specific issues related to timezones.

Matthieu Aubry mattab added this to the Piwik 0.6 milestone
Matthieu Aubry mattab self-assigned this
This issue was closed.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.