curious how people have solved this problem... I have a series of jobs that run overnight that roll up reports based on that day's data for customers. They're now asking for timezone support.
One of the reports is.. you had x number of orders last night, however last night could be different depending on timezone. What is the best way to organize or process the data so timezones are taking into account to make that job easier?
thanks
It is good practice to represent all the dates in the UTC time zone. This timezone has no confusing daylight savings time. Then the customer in the US/Pacific timezone can ask for a report on orders between 2010-09-20T00:00-700 to 2010-09-21T00:00-700 (using ISO 8601 format). The input layer of your program should convert both the customer dates and the stored order dates to seconds since the epoch in UTC and then go from there.
In my app, I save the local time plus the offset to UTC. This way, I can still compare the values (by converting them to universal time) in the code but when I display it on the screen, users will see the times they expect ("Yeah, I did that at 9:30 yesterday"). With a switch, they can show the timezone or switch to universal time or display all times in local time.
In my experience with this, we've rolled the data up into hourly (or fifteen minute) buckets during the nightly run. Then you can have the user requests grab the relevant buckets based on the time zone they are using for their report.