Is there a way to align time zone based on a zip without creating a related table?

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

I am trying to automatically set time zone in a field to align with the zip in another field. Is there a way to do this without creating a related reference table? 

Photo of Igor

Igor

  • 0 Points

Posted 4 years ago

  • 0
  • 1
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
There is a bit of a stretch but you could come up with a formula based on the fact that (1) each state has zip code ranges and (2) few just a few states straddle time zones. So from the zip code first determine the state and from the state next determine the time zone. Then correct for those zip code that straddle time zones (this part of the formula would need to be developed). I certainly did  not test this but here you go:

http://pastie.org/pastes/10282879/text
Photo of Igor

Igor

  • 0 Points
Wow this is really elaborate! Will give it a try, thanks for your help!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,074 Points 20k badge 2x thumb
Well good luck.Here are some notes that may help:

Notes

(1) There may be typos in my untested formula. I see both Zip and $Zip

(2) I *believe* the USPS has assigned one *continuous* range to zip codes in any state to allow for expansion but I have never seen this published by the USPS. So the disjoint ranges for say AR may be combined if you could locate the wider ragne:

  71601 <= $ZIP and $ZIP <= 72959, "AR",
  75502 <= $ZIP and $ZIP <= 75502, "AR",

(3) You have to find a source that map individual zip codes to time zones in those states that do not have one time zone per state. This could involve hundreds of zip code exceptions and I don't know a convenient way to find this information. If I had to do this task myself I would probably visit the Map Library at the University of Michigan - they are experts at this type of geographic mapping. There are weird anomolies as time zones and zip codes have nothing to do with each other. The one saving grace is that time zone boundries are probably drawn so as to avoid major population centers so even if the formula you eventually came up with was wrong it would probably only occur in low population centers

(4) You also have to account for daylight savings time and possibly Canada ...

(5) More good luck