Support for editing the site timezone #5476

mattab · 2008-01-03T23:38:24Z

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

mattab · 2008-06-30T00:07:24Z

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.

robocoder · 2009-02-15T21:19:18Z

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.

mattab · 2009-03-03T05:32:27Z

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
``````

robocoder · 2010-03-19T22:38:52Z

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

mattab · 2010-03-24T19:09:51Z

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

mattab · 2010-03-24T19:10:04Z

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

mattab · 2010-03-29T08:32:47Z

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

mattab · 2010-03-29T08:37:08Z

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.

mattab · 2010-03-30T14:57:14Z

Also

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

mattab · 2010-04-01T14:59:16Z

also

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

mattab · 2010-04-01T18:28:31Z

investigate potential bug when today's archives are not updated: http://forum.piwik.org/index.php?showtopic=7211

mattab · 2010-04-01T19:32:55Z

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-matomo-user · 2010-04-07T03:58:06Z

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

mattab · 2010-04-07T12:26:32Z

(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

robocoder · 2010-04-08T22:58:46Z

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

mattab · 2010-04-09T11:18:02Z

(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

mattab · 2010-04-10T18:28:12Z

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

mattab · 2010-04-10T19:50:43Z

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

mattab · 2010-04-12T08:05:54Z

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

mattab · 2010-04-12T09:30:12Z

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

mattab · 2010-04-12T11:53:42Z

(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

mattab · 2010-04-12T17:49:45Z

(In [2082]) Refs #5476

Also updated doc on http://piwik.org/docs/setup-auto-archiving/

robocoder · 2010-04-12T22:47:48Z

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

robocoder · 2010-04-12T23:12:25Z

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

robocoder · 2010-04-12T23:24:25Z

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.

robocoder · 2010-04-12T23:54:22Z

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

mattab · 2010-04-13T08:30:50Z

(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

mattab · 2010-04-13T08:47:19Z

(In [2095]) Refs #5476 fix typo

robocoder · 2010-04-27T19:57:26Z

php 5.1.6 user reported getting a notice:

http://forum.piwik.org/index.php?showtopic=7971&view=findpost&p=32671

see: http://us3.php.net/manual/en/language.oop5.magic.php#language.oop5.magic.tostring

mattab · 2010-05-04T13:32:15Z

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

mattab · 2010-05-05T10:52:13Z

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

mattab · 2010-05-05T10:58:26Z

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

mattab added this to the Piwik 0.6 milestone Jul 8, 2014

mattab added T: New feature labels Jul 8, 2014

mattab self-assigned this Jul 8, 2014

This issue was closed.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Support for editing the site timezone #5476

Support for editing the site timezone #5476

mattab commented Jan 3, 2008

mattab commented Jun 30, 2008

robocoder commented Feb 15, 2009

mattab commented Mar 3, 2009

robocoder commented Mar 19, 2010

mattab commented Mar 24, 2010

mattab commented Mar 24, 2010

mattab commented Mar 29, 2010

mattab commented Mar 29, 2010

mattab commented Mar 30, 2010

mattab commented Apr 1, 2010

mattab commented Apr 1, 2010

mattab commented Apr 1, 2010

anonymous-matomo-user commented Apr 7, 2010

mattab commented Apr 7, 2010

robocoder commented Apr 8, 2010

mattab commented Apr 9, 2010

mattab commented Apr 10, 2010

mattab commented Apr 10, 2010

mattab commented Apr 12, 2010

mattab commented Apr 12, 2010

mattab commented Apr 12, 2010

mattab commented Apr 12, 2010

robocoder commented Apr 12, 2010

robocoder commented Apr 12, 2010

robocoder commented Apr 12, 2010

robocoder commented Apr 12, 2010

mattab commented Apr 13, 2010

mattab commented Apr 13, 2010

robocoder commented Apr 27, 2010

mattab commented May 4, 2010

mattab commented May 5, 2010

mattab commented May 5, 2010

Support for editing the site timezone #5476

Support for editing the site timezone #5476

Comments

mattab commented Jan 3, 2008

mattab commented Jun 30, 2008

robocoder commented Feb 15, 2009

mattab commented Mar 3, 2009

robocoder commented Mar 19, 2010

mattab commented Mar 24, 2010

mattab commented Mar 24, 2010

mattab commented Mar 29, 2010

mattab commented Mar 29, 2010

mattab commented Mar 30, 2010

mattab commented Apr 1, 2010

mattab commented Apr 1, 2010

mattab commented Apr 1, 2010

anonymous-matomo-user commented Apr 7, 2010

mattab commented Apr 7, 2010

robocoder commented Apr 8, 2010

mattab commented Apr 9, 2010

mattab commented Apr 10, 2010

mattab commented Apr 10, 2010

mattab commented Apr 12, 2010

mattab commented Apr 12, 2010

mattab commented Apr 12, 2010

mattab commented Apr 12, 2010

robocoder commented Apr 12, 2010

robocoder commented Apr 12, 2010

robocoder commented Apr 12, 2010

robocoder commented Apr 12, 2010

mattab commented Apr 13, 2010

mattab commented Apr 13, 2010

robocoder commented Apr 27, 2010

mattab commented May 4, 2010

mattab commented May 5, 2010

mattab commented May 5, 2010