CODEWITHSUNDEEP
phpweb-servicespostgresql
user731136
user731136

Reputation:

Get the equivalent time between "dynamic" time zones

I have a table providers that has three columns (containing more columns but not important in this case):

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:

I should get the following results: idproviders = 1

If I execute the query in June, with this information:

I should get the following results: idproviders = 1 and 2

Upvotes: 1

Views: 867

Answers (2)

lanzz
lanzz

Reputation: 43158

You haven't given any details about your "region" records besides the fact that they have an ID.

Still, you can add a column timezone varchar NOT NULL to your regions table (I assume you have one). You will need to assign a time zone name to each region you have. This might not be a straightforward task.

After you do that, you can find which providers are within operating hours using the following query:

SELECT p.* FROM providers AS p LEFT JOIN regions AS r ON (r.id = p.region_id)
WHERE (now() AT TIME ZONE r.timezone)::time BETWEEN p.start_timestamp AND p.end_timestamp;

now() AT TIME ZONE r.timezone will convert the current server time to the timezone assigned to the region.

Example setting: Your server is in London and your provider is in Panama.
Example case 1: Your server's time is 2012-01-01 18:00:00. '2012-01-01 18:00:00' AT TIME ZONE 'America/Panama' will return 2012-01-01 13:00:00, the current time in Panama which you can compare against the provider's start_timestamp and end_timestamp.
Example case 2: Your server's time is 2012-08-01 18:00:00 and you observe DST. Panama, however, does not observe DST, since it is close to the equator and day length does not vary significantly. Still, '2012-08-01 18:00:00' AT TIME ZONE 'America/Panama' will return the correct current time in Panama: 2012-08-01 12:00:00, because Postgres knows that your time in August observes DST, and Panama's time doesn't.

I don't know how to make it any clearer for you.

Upvotes: 2

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656321

If you only have a time (as opposed to timestamp) and a region like "Texas", you are out of luck to begin with. The information whether this is supposed to be daylight saving time or not is not there. You cannot possibly know.

Make that field a timestamp with time zone (timestamptz), even if you are only going to use the time component. Thereby, whenever somebody enters a value, it is translated to an UTC timestamp internally (taking DST into account automatically) and you have unambiguous information. The value will be displayed according to the current timezone setting automatically (for everyone everywhere) and will be just right for everybody. When testing, you can simply cast it to time:

SELECT start_timestamp::time

Which gives you the equivalent local time (according to your current timezone setting). Makes everything very easy.
More explanation how PostgreSQL handles timestamps in this related answer.

Upvotes: 1

Related Questions