0

I'm aware there are similar questions like this (1), (2).

But I thing my approach is somewhat different.

I want to get the average times a user logs in the platform with it's timezone. This would be provided back to the user as some kind of stats, like:

You logged in an average of X times each day this week

As the timezone could mean the difference between the login being inside the week or not I've iterated through the problem with the following solutions with it's pros and cons.

My natural approach would be to use epoch time (ignoring timezones).

+-----------+--------------------+------------+
| log_in_id | user_id            | epoch_time |
+-----------+--------------------+------------+
| 1         | 1                  | 1513242884 |
| 2         | 1                  | 1513243489 |
+-----------+--------------------+------------+

But this wouldn't give accurate results as someone with a timezone not near UTC would appear to have logged on different days. And store somewhere the default user timezone.

So the next iteration of the problem would be to store the 1st login timezone of the user on to a table.

+--------------------+---------------+
| user_id            | timezone_id   |
+--------------------+---------------+
| 1                  | Europe/Madrid |
| 2                  | America/Bahia |
+----------- --------+---------------+

This solves one problem, but makes another one appear. Users with changing timezones (let's say they travel a lot or live in a zone with daylight saving time) would still not give accurate results.

The only solution I could thing of is to store the difference of timezone with each login.

+-----------+--------------------+------------+-----------------+
| log_in_id | user_id            | epoch_time | timezone_offset |
+-----------+--------------------+------------+-----------------+
| 1         | 1                  | 1513242884 | -60             |
| 2         | 1                  | 1513243489 | -120            |
+-----------+--------------------+------------+-----------------+

Now with epoch_time - time*60 would give the local time of each signin. But this surfaces another problem. A user could change to another time zone in less time than the timezone difference. Ej. User foo logs in at 14:00 local time then travels to B (that has -1h in the timezone) (takes 30 minutes) and logs in again at 13:30 (B local time), but it's really 30 mins later. This would look like an user traveled time.

At this point I don't know how to proceed. I suppose the last solution is enough and the "best" way to approach it. But I suppose there has to be a better way.

EndermanAPM
  • 327
  • 2
  • 22
  • What if you have a column TIMESTAMP UTC 0 and calculate the average by it? It wouldn't matter where the user is since the information you want would always be calculated in UTC 0. You can even have the specific timezone login. – Jorge Campos Dec 18 '17 at 16:36
  • I think thats just like my first approach. If done that way a user that lives far from UTC 0 would appear to be logging in two different days (when grouping by day) although he logged in in the same day (in it's current time). – EndermanAPM Dec 18 '17 at 16:47
  • Not at all, if your goal is have the average you just calculate the average in the UTC 0 and after the calculation is done you use it in the timezone the user is. – Jorge Campos Dec 18 '17 at 16:48
  • I think I have not stated my question properly, my bad, I want to calculate the average times the user logs in. If a user logs in at **UTC 0** 5:00 and 14:00 a group by day would return that the user logged in two times in the same date. But if the user lives in **UTC-11** the user really just logged in one time at 18:00 and 1 time at 3AM the next day. – EndermanAPM Dec 18 '17 at 16:55
  • Ok, you are right, this is not an average. And IMHO it makes no sense this kind of information because in such scenario you would be "hiding" security information based on the UTC the user is, And hide such type of information is really bad. You should edit your question with such example and a few more to clarify it more. – Jorge Campos Dec 18 '17 at 16:59
  • Also, on case of filtering the current month, the timestamp difference could mean the logins fall down on different months. – EndermanAPM Dec 18 '17 at 16:59
  • I don't see how I would be "hidding security information" as is no different that any analytics service. Also the intention would be to show the information back to the user. P.S. How would you clarify it? – EndermanAPM Dec 18 '17 at 17:02
  • Imagine an auditing event in your system that will show this information about the logins the users made into your system. A user that logs in at 1pm UTC 0 and logs in again at 1pm at UTC +1 logged in twice but given your requirements it was only once, is that right or I've understood it wrong, if so I apologize. But if not, such information represents the hiding scenario, I talked about. No security information should be disregarded by any means. – Jorge Campos Dec 18 '17 at 17:10
  • Yes, but no. In the 3rd scenario, the UTC time and the timezone are stored separately. So yeah, once merged, the user would look like it logged in twice at the same time, but checking just the UTC time it would be possible to see the time difference. – EndermanAPM Dec 18 '17 at 17:15
  • Yes, that's exactly what I'm proposing, you use the UTC 0 to calculate and the timezone specific to show where it was... you can even do the calculating based on a track of the timezones the user where. Like: In the UTC 0, logged in 1pm, 2pm and 3pm. But these events occurred in UTC 0, UTC 0 and UTC +1 so the average here would be 3 (the sum of UTC 0 events +/- (the hours of specific timezone, in this case +1) and everything divided by 3) which would be avg = 1,33 – Jorge Campos Dec 18 '17 at 17:26
  • 1
    Looks like we were talking about the same all along, just with different words – EndermanAPM Dec 18 '17 at 17:28
  • hahahahahhaha it happens. – Jorge Campos Dec 18 '17 at 17:37
  • So, this formula I gave, is it feasible to you? – Jorge Campos Dec 18 '17 at 17:37
  • Yeah, I suppose I'll have to live with the occasional overlapping times (when timezone is applied) – EndermanAPM Dec 18 '17 at 17:39

0 Answers0