Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Support for editing the site timezone #5476

Closed
mattab opened this issue Jan 3, 2008 · 32 comments
Closed

Support for editing the site timezone #5476

mattab opened this issue Jan 3, 2008 · 32 comments
Assignees
Labels
Critical Indicates the severity of an issue is very critical and the issue has a very high priority. Enhancement For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc.
Milestone

Comments

@mattab
Copy link
Member

mattab commented Jan 3, 2008

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).
  2. 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).
  3. 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.)
@mattab
Copy link
Member Author

mattab commented Jun 30, 2008

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
Copy link
Contributor

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
Copy link
Member Author

mattab commented Mar 3, 2009

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
Copy link
Contributor

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

@mattab
Copy link
Member Author

mattab commented Mar 24, 2010

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
Copy link
Member Author

mattab commented Mar 24, 2010

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
Copy link
Member Author

mattab commented Mar 29, 2010

(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
Copy link
Member Author

mattab commented Mar 29, 2010

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
Copy link
Member Author

mattab commented Mar 30, 2010

Also

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

@mattab
Copy link
Member Author

mattab commented Apr 1, 2010

also

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

@mattab
Copy link
Member Author

mattab commented Apr 1, 2010

@mattab
Copy link
Member Author

mattab commented Apr 1, 2010

  • 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
Copy link

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
Copy link
Member Author

mattab commented Apr 7, 2010

(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
Copy link
Contributor

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

@mattab
Copy link
Member Author

mattab commented Apr 9, 2010

(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
Copy link
Member Author

mattab commented Apr 10, 2010

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

@mattab
Copy link
Member Author

mattab commented Apr 10, 2010

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

@mattab
Copy link
Member Author

mattab commented Apr 12, 2010

(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
Copy link
Member Author

mattab commented Apr 12, 2010

(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
Copy link
Member Author

mattab commented Apr 12, 2010

(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
Copy link
Member Author

mattab commented Apr 12, 2010

(In [2082]) Refs #5476

@robocoder
Copy link
Contributor

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

@robocoder
Copy link
Contributor

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

@robocoder
Copy link
Contributor

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
Copy link
Contributor

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

@mattab
Copy link
Member Author

mattab commented Apr 13, 2010

(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
Copy link
Member Author

mattab commented Apr 13, 2010

(In [2095]) Refs #5476 fix typo

@robocoder
Copy link
Contributor

@mattab
Copy link
Member Author

mattab commented May 4, 2010

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

@mattab
Copy link
Member Author

mattab commented May 5, 2010

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

@mattab
Copy link
Member Author

mattab commented May 5, 2010

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

@mattab mattab added this to the Piwik 0.6 milestone Jul 8, 2014
@mattab mattab self-assigned this Jul 8, 2014
This issue was closed.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Critical Indicates the severity of an issue is very critical and the issue has a very high priority. Enhancement For new feature suggestions that enhance Matomo's capabilities or add a new report, new API etc.
Projects
None yet
Development

No branches or pull requests

3 participants