Get the equivalent time between "dynamic" time zones
- by doctore
I have a table providers that has three columns (containing more columns but not important in this case):
starttime, start time in which you can contact him.
endtime, final hour in which you can contact him.
region_id, region where the provider resides. In USA: California, Texas, etc. In UK: England, Scotland, etc
starttime and endtime are time without timezone columns, but, "indirectly", their value has time zone of the region in which the provider resides. For example:
starttime | endtime | region_id (time zone of region) | "real" st | "real" et
----------|----------|---------------------------------|-----------|-----------
03:00:00 | 17:00:00 | 1 (EGT => -1) | 02:00:00 | 16:00:00
Often I need to get the list of suppliers whose time range is within the current server time (taking into account the time zone conversion). The problem is that the time zones aren't "constant", ie, they may change during the summer time. However, this change is very specific to the region and not always carried out at the same time: EGT <= EGST, ART <= ARST, etc.
The question is:
1. Is it necessary to use a webservice to update every so often the time zones in the regions? Does anyone know of a web service that can serve?
2. Is there a better approach to solve this problem?
Thanks in advance.
UPDATE
I will give an example to clarify what I'm trying to get. In the table providers I found this records:
idproviders | starttime | endtime | region_id
------------|-----------|----------|-----------
1 | 03:00:00 | 17:00:00 | 23 (Texas)
2 | 04:00:00 | 18:00:00 | 23 (Texas)
If I execute the query in January, with this information:
Server time (UTC offset) = 0 hours
Texas providers (UTC offset) = +1 hour
Server time = 02:00:00
I should get the following results: idproviders = 1
If I execute the query in June, with this information:
Server time (UTC offset) = 0 hours
Texas providers (UTC offset) = +2 hours (their local time has not changed, but their time zone has changed)
Server time = 02:00:00
I should get the following results: idproviders = 1 and 2
