Opened 6 years ago

Closed 4 years ago

#56 closed New feature (fixed)

Support for editing the site timezone

Reported by: matt Owned by: matt
Priority: critical Milestone: Piwik 0.6
Component: Core Keywords:
Cc: Sensitive: no

Description (last modified by matt)

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.

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).

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).

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 :)

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

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.)

Change History (44)

comment:1 Changed 6 years ago by matt (mattab)

  • Milestone set to Future features

comment:2 Changed 6 years ago by matt (mattab)

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.

comment:3 Changed 6 years ago by matt (mattab)

  • Milestone changed from Future features to DigitalVibes

comment:4 Changed 5 years ago by matt (mattab)

  • Milestone changed from DigitalVibes to Stable release

comment:5 Changed 5 years ago by matt (mattab)

  • Priority changed from low to major

comment:6 Changed 5 years ago by vipsoft (robocoder)

  • Milestone changed from Stable release to Surviving The Wild

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.

comment:8 Changed 5 years ago by matt (mattab)

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

comment:9 Changed 5 years ago by matt (mattab)

  • Description modified (diff)

comment:10 Changed 5 years ago by matt (mattab)

  • Description modified (diff)

comment:11 Changed 5 years ago by alivenk

comment:12 Changed 5 years ago by albass

comment:13 Changed 5 years ago by vipsoft (robocoder)

  • Milestone changed from 3- Surviving The Wild to 2- DigitalVibes
  • Sensitive unset

comment:14 Changed 5 years ago by domtop

comment:15 Changed 4 years ago by john435

comment:15 Changed 4 years ago by vipsoft (robocoder)

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

comment:16 Changed 4 years ago by matt (mattab)

  • Milestone changed from 2 - Piwik 0.7 - DigitalVibes to 1 - Piwik 0.6
  • Owner set to matt
  • Priority changed from major to critical

comment:17 Changed 4 years ago by matt (mattab)

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

comment:18 Changed 4 years ago by matt (mattab)

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

comment:19 Changed 4 years ago by matt (mattab)

(In [2006]) Refs #56

  • 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.

comment:20 Changed 4 years ago by matt (mattab)

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.

comment:21 Changed 4 years ago by matt (mattab)

Also

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

comment:22 Changed 4 years ago by matt (mattab)

also

  • disable GeoIp, SearchEnginePosition on update, as it breaks the plugins. Put a message asking user to upgrade.

comment:23 Changed 4 years ago by matt (mattab)

comment:24 Changed 4 years ago by matt (mattab)

  • 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

comment:25 Changed 4 years ago by spliffhead

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

comment:26 Changed 4 years ago by matt (mattab)

(In [2058]) Refs #56

  • 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

comment:27 Changed 4 years ago by vipsoft (robocoder)

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

comment:28 Changed 4 years ago by matt (mattab)

(In [2067]) Refs #56

  • 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

comment:29 Changed 4 years ago by matt (mattab)

  • Visits by server time should report time in website timezone rather than UTC

comment:30 Changed 4 years ago by matt (mattab)

  • Time displayed in Live! widget should be in the website timezone

comment:31 Changed 4 years ago by matt (mattab)

(In [2078]) Refs #56

  • 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.

comment:32 Changed 4 years ago by matt (mattab)

(In [2079]) Refs #56

  • 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)

comment:33 Changed 4 years ago by matt (mattab)

(In [2081]) Refs #56

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

comment:35 Changed 4 years ago by vipsoft (robocoder)

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

comment:36 Changed 4 years ago by vipsoft (robocoder)

(In [2087]) refs #56 / [2058] - add timezone selection to webtest

comment:37 Changed 4 years ago by vipsoft (robocoder)

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.

comment:38 Changed 4 years ago by vipsoft (robocoder)

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

comment:39 Changed 4 years ago by matt (mattab)

(In [2091]) Refs #56

  • 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

comment:40 Changed 4 years ago by matt (mattab)

(In [2095]) Refs #56 fix typo

comment:42 Changed 4 years ago by matt (mattab)

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

comment:43 Changed 4 years ago by matt (mattab)

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

comment:44 Changed 4 years ago by matt (mattab)

  • Resolution set to fixed
  • Status changed from new to closed

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

Note: See TracTickets for help on using tickets.