What is the best best way to store time zone data. We have a client database and we want to store what time zone each client resides in. We may use it later for appointment scheduling.
We ended up creating a timezone table and stored the UTC offset and a description. The user applies a time zone to our clients and our staff tables. We save all time in database in our home office time zone(Mountain) All correspondence and reporting that goes to clients or staff is converted to receivers local time zone using formulas based shamblee page reference
It may be a time field. You can subtract it from local timestamp, then get GMT timestamp.
If client is travelling, you might want to evaluate his actual position at login.
A US client being in Europe for a month should not see his current appointments being related to his Oklahoma residence.
That is a good question.
How many time zones are you dealing with.
Do you need to know only how is different from home base.
Are you only making appointments during the time home office is open and need to know the time in the client's timezone.
I would probably just the standard timezone offset number i.e. -6 but, you could just assign your timezone as 0
(Eastern +1; Central 0; Mountain -1, Pacific -2) and add it to your local time (hour) to get the time in the client's timezone.
We are only dealing with North America at present, but that may expand. We set appointments between clients and staff members. Staff members may be based in different time zones as well. We will schedule in one time zone but confirmation emails got to staff and client and they may both be in a different time zone. I am thinking that storing the standard offsets may be best and allow for growth later.
So it look like your biggest concern is getting emails to your staff and clients in a timely fashion so appointments are not missed. That make the time at the appointment location the most important and then offset to home office time to make the time the email needs to be sent by.
I would suggest a reference table based upon state code, which should also take Daylight saving time into account.
That is not easy, many states have more than one timezone and no easy way to know which side an address is on. the USPS data is the best source for that info.
Retrieving data ...