Support for editing the site timezone
|Reported by:||matt||Owned by:||matt|
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.
- 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).
- 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).
- 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 :)
- 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
- 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:3 Changed 5 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:6 Changed 5 years ago by vipsoft (robocoder)
- Milestone changed from Stable release to Surviving The Wild
comment:13 Changed 4 years ago by vipsoft (robocoder)
- Milestone changed from 3- Surviving The Wild to 2- DigitalVibes
- Sensitive unset
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