The frontend specifies some parameters they want the data to start and end from, including the timezone. e.g.
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?