Storing statistics over longer periods of time and timezones

Question

Currently I'm working on a project which calculates data and stores it by ID in a analytics database.

For example the amount of times a news article is read (and so there are like 20 categories of data stored as integer).

We store the data in fields as followed: int user_id int value_type_id int value datetime datetime

We use 4 tables, x_hour, x_day, x_week, x_month This way we won't have to calculate the data over a potential few thousands or even millions of records.

The data needs to be calculated on the fly and filtered by certain joins. This is all no problem and works as intended and at a speed which is satisfactory.

The issue that follows. We want the data to display in the timezone of the user who views it, the timezone is not always the same since it can be antying, for example UTC-5 or UTC+4.

Since we store the dates on UTC we are having problems with intervals on days,weeks and months since if activity is stored an hour before midnight the larger intervals will see it as yesterday, even though it could be on the same day in that timezone.

I've read solutions as adding 24 columns to hold the data for every timezone, does anyone have a different solution.

Answer 1

Continue to store the datetimes in UTC.

Pass in the user's timezone to the query.

Convert in the SELECT, using the CONVERT_TZ function:

CONVERT_TZ(`datetimefield`, 'UTC', 'Europe/Amsterdam')

Where 'Europe/Amsterdam' is replaced with the appropriate timezone.

You're better off using IANA timezone strings as above, instead of offsets like 'UTC-5', as long as you have this data available. It will correctly handle issues surrounding daylight savings in the regions where this takes place.

Further notes: https://dev.mysql.com/doc/refman/5.5/en/mysql-tzinfo-to-sql.html - This program is used to intialise MySQL with timezone data.