1

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

doctore
  • 3,855
  • 2
  • 29
  • 45
  • 1
    Why don't you just use the "timestamp with time zone" data type and let the system handle issues such as DST? – Kouber Saparev Sep 18 '12 at 12:09
  • Hi @KouberSaparev, the reason is because the data of starttime and endtime columns will not be linked directly to the time zone, ie, if the provider puts 14:00:00 to 17:00:00 in January (no daylight saving), those hours "are the same" in June. Stated otherwise, if I convert the type to time with timezone, at 14:00:00+1 in January, it should then changed to 14:00:00+2 in summer time. – doctore Sep 18 '12 at 12:41
  • The problem is that I need the equivalence between the server time (in EGT for example) and the range that has put the provider, whose time zone could be, for example, ARST or ART (depending on current date). – doctore Sep 18 '12 at 12:58
  • `the range that has put the provider` .. What's that supposed to mean? And `ART` is supposed to be Argentinian Time and EGT Eastern Greenland Time? Don't be cryptic when asking for advice. Edit your question with important information, don't put it in comments. – Erwin Brandstetter Sep 18 '12 at 13:01
  • Hi @ErwinBrandstetter. 'The range that has put the provider' = values of starttime and endtime. ART = Argentinian Time. EGT = Eastern Greenland Time. – doctore Sep 18 '12 at 13:25
  • @doctore If the local timezone of your provider changes between +1:00 in the winter and +2:00 in the summer, then 14:00:00+1 in January is the same time as 14:00:00+2 in August. – lanzz Sep 18 '12 at 14:37
  • Hi @lanzz, for this reason I don't use a time with timezone type. However, I need to know the time difference between the server where I launch the query and the time zone where the provider resides, because I need to know if the actual time of server is in the range (starttime - endtime) put by provider. And the time zone of the server and provider are dynamic. – doctore Sep 18 '12 at 15:01
  • @doctore This I have already told you in my answer. – lanzz Sep 18 '12 at 15:03
  • @lanzz I updated the question and I put an example to clarify my case – doctore Sep 18 '12 at 15:19

2 Answers2

2

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.

lanzz
  • 42,060
  • 10
  • 89
  • 98
  • Hi @lanzz, the problem isn't the query (I know that PostgreSQL has support for time zones). The problem is how I know the actual time zone, because for the same region may vary at certain times. – doctore Sep 18 '12 at 12:04
  • PostgreSQL timezones are _not_ what you think they are. You don't specify timestamps as being in ART or ARST, you specify them as being in, say, the "America/Argentina/Buenos_Aires" timezone. The actual time offset then will depend on the specific timestamp and the DST rules defined for Buenos Aires. Nothing stops you from comparing timestamps from different timezones, e.g. `timestamp1 AT TIME ZONE 'America/Argentina/Buenos_Aires' BETWEEN timestamp2 AT TIME ZONE 'America/New_York' AND timestamp3 AT TIME ZONE 'America/New_York'`. – lanzz Sep 18 '12 at 14:31
  • Hi @lanzz, +1 but I still have a doubt, in the code: `now() AT TIME ZONE r.timezone` if the timezone of region is ART, for example, does PostgreSQL would know when to "change it" to ARST, or I would have to be updated periodically time zones using a webservice for example? – doctore Sep 19 '12 at 07:27
  • `r.timezone` shouldn't be `ART` but `America/Argentina/Buenos_Aires`. That's why I described that you'll need to assign an IANA timezone to each region, and _not_ use timezones like ART/ARST. – lanzz Sep 19 '12 at 09:39
1

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.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks for your answer, but not what I'm looking for. As I was telling to @lanzz, I know that PostgreSQL has support for time zones and I use it in other cases. But if, for example, I launched the consultation in May and the server's time zone is EGT and the argentinean provider is ART, the time difference is 2 hours, but if I do it in November, the difference becomes of 1 hour (because the "time zone" of Argentina is ARST). – doctore Sep 18 '12 at 13:50
  • Indeed, the difference between time zones is not constant, but that's what you actually need at the end - a way to point exactly at 2 p.m. EGT, regardless of the season or PostgreSQL server physical location and local zone. – Kouber Saparev Sep 18 '12 at 14:18
  • @KouberSaparev If business hours are 9:00-18:00 local time, then those times actually change with DST, compared to UTC. It won't do to keep the same value regardless of the season. – lanzz Sep 18 '12 at 14:40
  • @lanzz: The local *times* do not change. The time offset compared to UTC changes. My proposed regime takes DST into account. The result of `start_timestamp::time` (`start_timestamp` being of type `timestamptz`) automatically adapts to DST (on both ends: input and output), because it casts according to the local timezone setting. – Erwin Brandstetter Sep 18 '12 at 14:50
  • It has to adapt not only to the local (server's) DST rules, but to the providers' local DST rules as well. – lanzz Sep 18 '12 at 14:52
  • If you want to find providers that are open at `14:00` (your *local* wall clock) this query just works for everyone, everywhere: `SELECT * FROM providers WHERE '14:00'::time BETWEEN start_timestamp::time AND end_timestamp::time`. – Erwin Brandstetter Sep 18 '12 at 15:00
  • @ErwinBrandstetter I updated the question and I put an example to clarify my case. – doctore Sep 18 '12 at 15:21
  • @ErwinBrandstetter You want to find providers that are open _at the moment_; i.e. _their local time_ has to be between `start_timestamp` and `end_timestamp` (which are also given in _their local time_), not _your local time_. – lanzz Sep 18 '12 at 18:34