1

I create some basic analytics for my site. A registered user has their own page. I provide them with some basic analytics for page views: View count, referrer, browser, OS and country. The user can filter by hour, day, week, month and all. I aggregate all views for the day and then for the month. The problem I have is the data is being stored with UTC timezone which is fine until I start to aggregate the data. Eg. A UTC day is different to an EST day, which means the analytics is not be correct for a user outside the UTC timezone when filtered by week, month and all.

The reason I'm aggregating the views is so the views table doesn't get too big. All views for the day are condensed into one row for a specific page and then again condensed into one row for the month.

How can I solve this problem?

Oliver
  • 43
  • 6
  • 1
    If you have users in more than a single time zone, I don't think you are going to be able to pre-aggregate the data. You'll need to aggregate the data on demand, based on the user's definition of a shift, a day, a month, etc. – Flydog57 Jan 24 '19 at 20:17
  • @Flydog57 Not sure how to solve this then, perhaps a MapReduce? Never done anything like this before. Logging views and aggregating on demand could be a lot of data and be slow. Any ideas or can you point me in the right direction? Thanks – Oliver Jan 25 '19 at 18:54
  • Pick a standard "day" - whether it be UTC-based, or where most of your users are, or the geographic center (like if they are mostly US users, clustered on the coasts, pick, say "Central Time". Note, for example, that everything on the Stack Overflow site is measured in UTC days. Another choice would be multiple pre-aggregations (Americas day, European Day, Central Asian Day). – Flydog57 Jan 25 '19 at 19:41

1 Answers1

0

Why not using DateTimeOffset?

DateTimeOffset consider instantaneous time.

Instantaneous time

https://learn.microsoft.com/en-us/dotnet/standard/datetime/choosing-between-datetime

DateTime vs DateTimeOffset

The DateTimeOffset structure represents a date and time value, together with an offset that indicates how much that value differs from UTC. Thus, the value always unambiguously identifies a single point in time.

The DateTimeOffset type includes all of the functionality of the DateTime type along with time zone awareness. This makes it suitable for applications that do the following:

Uniquely and unambiguously identify a single point in time. The DateTimeOffset type can be used to unambiguously define the meaning of "now", to log transaction times, to log the times of system or application events, and to record file creation and modification times.

Perform general date and time arithmetic.

Preserve multiple related times, as long as those times are stored as two separate values or as two members of a structure.

Igor Quirino
  • 1,187
  • 13
  • 28