I am currently working on a project that calculates data and stores it by identifier in the analytics database.
For example, the number of times a news article is read (and thus, as 20 categories of data stored as an integer).
We save the data in the following fields: int user_id int value_type_id value int datetime datetime
We use 4 tables, x_hour, x_day, x_week, x_month Thus, we do not have to calculate data for several thousand or even millions of records.
Data should be calculated on the fly and filtered by specific associations. This is not a problem and works as intended and at a speed that is satisfactory.
The next question. We want the data to be displayed in the time zone of the user who is viewing it, the time zone is not always the same, since it can be an anti-virus, for example, UTC-5 or UTC + 4.
Since we store dates in UTC, we have problems with intervals of days, weeks, and months, because if the activity is stored one hour before midnight, then large intervals will see it as yesterday, although it may be on the same day at this hour belt.
I read the solutions, adding 24 columns to store data for each time zone, someone has a different solution.