I’m running a Django application service with an API that feeds to a javascript frontend. The data is specifically for charts and graphs, so the data that is returned is a fairly large json with every call.
-
The frontend specifies some parameters they want the data to start and end from, including the timezone. e.g.
GET /pets?start_date=2015-01-01T05%3A00%3A00.000Z&end_date=2016-01-01T05%3A00%3A00.000Z?company_id=345&group_by=pet_type -
The data it’s fetching is aggregate data. We created tables that store this aggregated data for performance. e.g. basically these are denormalized tables, which are star tables.
-
We broke down the dates we are querying on for performance in another table and join on these tables for querying specific time intervals. e.g.
actual_date day month year day_of_year quarter
2015-04-10 10 4 2015 69 2
....
All dates are stored in UTC.
I would naively think that the frontend would request in UTC and the service could return the data in UTC. However this is a massive amount of graph data per call, so the frontend doing this calculation is not optimal.
The question becomes, if the frontend requests data in a specific timezone, how to best return aggregated data specific to that timezone?
1
The database layer certainly shouldn’t be involved- it should be UTC and UTC only, unless there’s some special logic about timezones involved instead of just converting the times.
In an ideal world, if the client wants to display data in non-UTC, it should perform the conversion there, as this is more of a display/rendering detail.
However, if for some reason it’s computationally non-viable to calculate on the client, then move it up only as far as you need- e.g. if you need to do it on the server, then do so at the controller/request handler level.