mysql - How to model a database to handle counters on multiple timezones -

I have a table that has analytical information, that is: Page view on each page.

  Field type --------------------------- Page_ id Long time created time (era (UTC - rotating hours) Page_view tall   
 I give the era down to hour (example: 1398456553 ==> 1398456000), which gives this table the information collected per hour.  
 When a customer requests his data, we allow the appropriate adjustments to view the data in his local timezone.  
 If the client's local timezone is UTC, then the query is simple:  
  SELECT FROM_UNIXTIME (St. Curated_time ','% Y-% m-% d ') From page_views as Created_at, SUM (St. Page_views`) `St. Curated_time 'between 13 96310400 - 01 April 2014 00:00:00 GMT and 1397088000 - April 10, 2014 00:00:00 GMT created by Group;   
 If the client's timezone is elsewhere (Example: -03: 00), then the query needs a little more manipulation to adjust the dates to correct tees:  
 SELECT DATE_FORMAT (CONVERT_TZ (FROM_UNIXTIME (St. Curated_time `), '+00: 00', '-03: 00', '% Y-% m-% d') From page_stats as 'Same (st `page_view'), 'Page_State' from St. St. where is the Central Consultant - 1396321200 - 01 April 2014 03:00:00 GMT and 13970 9 8800 - April 10, 2014 03:00: GMT GM Created by Gya_att;   
 This approach works fine for short duration (& lt; 30days), but it is bad when the date range represents several months, because the number of rows should be selected Due to changes such as  DATE_FORMAT  by  
  
  data granularity , but I can not make an aggregate table by day According to the day, the rollup is different at each TZ.  
 What should be the correct way of model tables to provide TGG integrity on large tablesets?  
 Its remarkable, I can allow some error (& lt; 2%) on this, by group, maybe can help solve some problem but I do not know yet.   
 
 
 First, note that timezone! = See offset.  
 Second, if you are collecting from the date of the target in many time zones, then you can select only some relevant time zones and pre-pack your local dates in unique columns in your data. . Then it will be a little easier at query time. Of course, this strategy does not stop if you want to support all 500+ time zones.  
 Another strategy is to create another set of tables, which is a 15-minute bucket. Why 15 minutes? Because all time zones are not offset in full hour context. Ideas-4: 30 is used in Venezuela, +5: 30 is used by India, +5: 45 is used in Nepal, and +8: 45 is used in some parts of Australia once your After these pre-set passes, you can convert  those  to specific client timezone details at query time.  
 And in the end, you can think that like a relational database, MySQL may not be the best tool for this particular job, an OLAP cube will work quite well, and therefore some of the many nodes Can also reduce a map / function in the database. You want to repeat your data with MySQL in a different "Reporting Store" or "Data Warehouse", and you can query from there.