Watching the user - two types of timestamp

Once upon a time, one database specialist (from those bearded and already gray) told me that timestamps are the most complex topic in databases. I really didn’t believe him, but the jokes actually occur over time.

There is a standard problem that I often see in other people's data. Let's put you going to track events / user actions. Usually you will have to do some code (JS in the web or SDK for app), which will send data to the server.

Every event needs a time stamp. And there is a choice of two: local time on the client or the time the event was received by the server. One good piece of advice to do and no answer under the cut.
')

Server time:

• Plus: Full control over the accuracy of time, data format and time zone. Everything is standard, everything works.
• Minus: Network lags affect the timestamp. Moreover, if this is a smartphone application, then surely you are using data download in batches to minimize network usage. Typically, user events are stored locally until they are enough (for example, 10) and then merged all at once. Of particular importance is this tactic, if you work with emerging markets, where more than half of devices are the cheapest Android connected via EDGE. As a result, the data comes in batches and they have the same timestamp for all. Understand the order and the time between events does not work. Here, by the way, another similar example from Habr.

Customer time:

• Plus: Provides accurate data on the order of events at the client and the time between them
• Minus: You will be surprised how often the users on the device have the wrong time!

In my experience, from 1% to 5% of users (I look at different projects and audiences) live in the distant past or even in the future. I honestly do not understand why they do it.



I noticed that there are especially many such users in the Philippines and in Japan.



This problem is a real nightmare. It breaks all requests for user activity in general, and these are the most popular questions in analytics.

One possible solution is to create your own time counter on the client. Ask the server for any time on the Internet and remember the difference with the system time. But this is a dreary business and there is no guarantee that the shift will be stable (there is no guarantee that something is wrong with this device on this device).

So what to do? Just keep both time stamps and use more appropriate depending on the situation. As always, more data is better. But this is often forgotten. Then I write, so as not to forget.

PS Questions to the audience:

• Other methods?
• Any idea what's wrong with the Japanese and Filipinos?