Applying user timezone to a data aggregation APIs

2 min readOct 31, 2020

In this article, I want to explain how to build a data aggregation API that will show data in a user time zone. Also, it will support time zone change without data modification.

So, for example, there is a telecom carrier and we want to provide user info about the number of messages that were sent in some time period. For simplicity let’s say we have only a possibility to group by day.

Here we won’t cover frontend work needed to support this feature and will concentrate only on the backend.

Presentation layer

Rest API to provide chart data may look like:

/api/v1/messages/chart?from=2020–05–10&to=2020–05–16

and it will return a list of messages counts aggregated by days:

Data layer

To store messages data we will have a relational database with the next structure:

Dates in data storage are persisted in UTC timezone. We will split the implementation of aggregation queries into two parts:

results filtering
results aggregation

Messages filtering

It’s pretty simple. Our method input dates from and to are passed in user timezone. Let’s imagine that the current date is 2020–10–25 04:35 UTC and our user is located in Los Angeles where the local time zone is PDT (UTC-7). So for Los Angeles customers current date is 2020–10–24 21:35 PDT.

To filter dates correctly we need to apply timezone conversion to our SQL queries. Here is how MySql query might look like:

Messages aggregation

The next step is to group messages by day and count the number of messages sent. That is where things are getting a little bit trickier. The first solution you come to maybe like:

But it is actually a wrong query as it will give you result in UTC, but filtered using the local time zone. Let’s run a query to show a message creation date in user timezone PDT.

So now we see that in user timezones we have a different number of messages sent per day. 3 messages on 2020–05–12, 1 on 2020–05–13 and 1 on 2020–05–14.

On the final step, it is needed to add timezone conversion to a creation date projection:

Conclusion

This data aggregation solution works well on small amounts of data, but for a huge table I would recommend adding some additional tables to store intermediate aggregates. We can also easily customize the current solution with other aggregation intervals (like year, month or hour) by changing the date format pattern.