Time Zone Issues

[mikeracicot]

I'm looking for a consensus of opinion on how to deal with multiple time zones in an ecommerce app. What makes the most sense especially when dealing with revenue implications from timing issues. I would like to know what others have done with this and how easy or difficult was it to overcome any obstacles. I do realize that there are a multitude of ways to do this and that is why I am asking all of you. Thanks and Cheers.

 

[sleipnir214]

Define what you mean when you say "deal with multiple time zones in an ecommerce app".

Do you mean, how do you display local time on a user's machine? ______________________________________________________________________
TANSTAAFL!

 

[mikeracicot]

From an accounting standpoint when you start the timer on a lease at a location in the midwest but the servers reside in the east( yeah, I know it's simple math) But wait the plot sickens. If a user with a profile from the midwest decides to come to the east and logs in to the app and is presented with the time from the midwest how would that be handled. Personally I see this part as of no consequence but I was asked to do a little research on this subject.

Here is part of an article I found by Ralph Kimbel P.H.d.
".
Synchronizing Multiple Time Zones
Many businesses measure the exact time of their basic transactions. The most common measured transactions include retail transactions at conventional stores, telephone inquiries at service desks, and financial transactions at bank teller machines. When a business spans multiple time zones, it is left with an interesting conflict. Does it record the times of these transactions relative to an absolute point in time, or does it record the times relative to local midnight in each time zone? Both of these perspectives are valid. The absolute time perspective lets us see the true simultaneous nature of the transactions across our entire business, whereas the local time perspective lets us accurately understand the transaction flow relative to the time of day. In the United States, “everyone” gets off work at 5 p.m., watches the news at 6, and eats dinner at 6:30.
It’s tempting to store each underlying transaction with an absolute timestamp and leave it up to the application to sort out issues of local times. Somehow, this seems to be a conservative and safe thing to do, but I don’t support this design. The database architect has left the downstream application designer with a complicated mess. Doing a coordinated local-time-of-day analysis across multiple time zones is nightmarish if all you have is a single absolute timestamp. Transaction times near midnight will fall on different days. Some states, such as Indiana and Arizona, do not observe daylight savings time. Reversing the design decision and storing the transaction times as relative to local midnight just recasts the same application problem in a different form. What we need instead is a more powerful design.
See Figure 1 for the timestamp design I recommend for businesses with multiple time zones. The timestamp is recorded simultaneously in both absolute and relative formats. Additionally, I recommend separating the calendar day portions of the timestamps from the time-of-day portions of the timestamps. We end up with four fields in a typical transaction fact table. The two calendar-day fields should be surrogate keys pointing to two instances of a calendar-day dimension table. These key entries in the fact table should not be actual SQL date stamps. Rather, these keys should be simple integers that point to the calendar date dimension table. Using surrogate (integer) keys for the actual join lets us deal gracefully with corrupted, unknown, or hasn’t-happened-yet dates. We split the time of day from the calendar date because we don’t want to build a dimension table with an entry for every minute over the lifetime of our business. Instead, our calendar day dimension table merely has an entry for every day. In any case, we don’t have unique textual descriptors for each individual minute, whereas we do have a rich array of unique textual descriptors for each individual day.
The two time-of-day fields are probably not keys that join to dimension tables. Rather, they are simply numerical facts in the fact table. To constrain such time-of-day facts, we apply BETWEEN constraints to these fields. If we do a lot of these kinds of constraints, it will be helpful to build an index on each of these of these time-of-day fields.
Although this double-barreled design uses a bit more storage space (three extra fields) in the fact table, the application designers will be delighted. Both absolute and relative time analyses will “fall out” of the database, regardless of how many time zones your business spans."
The table he used for figure 1:
1.date_key(FK)
2.GMT_date_key(FK)
3.product_key(FK)
4.customer_key(FK)
5.call_center_key(FK)
6.service_rep_key(FK)
7.promotion_key(FK)
8.time_of_day
9.GMT_time_of_day
10.dollars_sold
11.units_sold
12.dollars_cost
"Comparing sales across multiple time zones requires two date dimensions and two time of day facts."

Is there are smarter,simpler,easier way to account for these kinds of issues? Thanks again.

 

[fhlee]

Hi,
Wow ... that was a long post... Sorry I didn't finish reading it. But I felt compeled to post this anyway:

What you need is a universal time. Irregardless on where your server and customers are located, the best practise is to store all date/time field using a Universal Coordinated Time (UTC --dunno why it is UTC instead of UCT). Basically this is the GMT time. On your server, store all date/time in GMT and when presenting the date to the user, convert it to the User's local time by looking up the user's profile (for the TimeZone setting).

This way, it simplifies a whole lot of programming and accounting issue, because you now have a standard date/time. Then, how you want to treat your accounting and pull record off (local or universal) will be easy to do, just offset the GMT (UTC) with the user's local TimeZone setting. This can be done easily.

TimeZone can be a major headache especially if you write programs that are used by many users across the globe and running off multiple servers also spread across the globe. By standardizing your date/time field, all problems will no longer be a technical in nature, but a business decision.

Hope this helps.

regards,
- Joseph ~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Visit -->

http://www.replicaSi.com

for (Replica) Watches, Pen, Handbags, Hats, Jerseys and more ... at prices that makes your code spin ...
~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

 

[remid]

Hi guys,

storing in UTC with a TimeZone setting for each user as fhlee suggested is a good solution but only when, in time, a user does not change this setting. If the TimeZone setting changes, the timestamp of a transaction changes also.

The only thing I can think of is keeping a transaction TimeZone setting for each transaction.

Regards,
Rémi

 

[remid]

...

or maybe just a TimeZone history would be enough.
Store the dates in UTC and keep track of when the TimeZone setting changes. When you want to display a date, check against the time zone history to pick the time zone that was set at the specified date.

just sharing my thoughts,
Rémi

 

[jnicho02]

Ralph Kimball's advice is interesting from a Data Warehousing perspective, i.e. if you want to analyse your figures then you need the relative date and time, but for a transaction system you need a universal time.

The point of storing the relative date and not having to work it out is that he is going to point an OLAP tool at the data.

Often, "universal time" will be the time on the server....this has dangers with someone resetting the server clock or changing it for daylight saving, etc. Jeremy Nicholson, Director of

http://www.firstlightassociates.co.uk

a UK-based Java and Data Warehousing consultancy