How to Build a Zip Code to Time Zone Database in 5 Steps

Timing matters when it comes to contacting customers. It increases the engagement and response. However, determining the right time for a large base of customers spread across different zones is challenging, especially in countries like the USA, Canada, Mexico, Brazil, and Russia, which span multiple time zones.

In this article, we’ll infer time zones from international postcodes. Zip codes (also known as postal codes or postcodes) are an element of standard address formatting. Since they are part of user information, zip codes are often used to determine time zones.

🔗 Download free zip code to time zone Excel sample here. For over 15 years, we’ve built a GeoPostcodes worldwide zip code database from over 1,500 sources.

What Is Time Zone By Zip Code Mapping?

It is a process of associating a time zone to each postal code of a dataset. This requires access to:

• Zip codes with additional information (administrative divisions, coordinates, etc.)
• Time zone rules

Mapping zip codes to time zones can be complex, especially in the USA, where states, counties, or even smaller regions can have more than one time zone. To accurately map zip codes, you need to find a source that correlates zip codes with states/counties, assigns time zones to states/counties, and encodes exceptions where a zip code falls into multiple time zones.

Rules get more complicated when you target international data, which is the purpose of this article. So, we will not work with individual time zone rules but use a more convenient method for global data, focusing on geographical matching between postcodes and mapped time zones.

Assigning Time Zones to Zip Codes

We’ll discuss where to find the data, how to upload it, and how to use it to assign time zones to postcodes.

Sourcing Postcode Data

You first need to identify a good source of international postcodes, with geographical information. Free candidates include GeoNames and OpenStreetMap.

From those sources, you can access postal codes for a number of countries (and even more if you combine them) along with their latitude and longitude. This tutorial will use GeoNames.

💡 Open source data is often unreliable, incomplete, and lacks regular updates to catch up with zip code changes. Our location data is updated weekly, relying on more than 1,500 sources. Download the free zip code to the time zone Excel sample here.

Sourcing Time Zone Data and Daylight Saving Time

Internet Assigned Numbers Authority publishes the most widely used time zone data, including rules and codes for capturing different time zones and accessing past and future time switches from Standard Time to Daylight Saving Time.

We’ll use a map of the official IANA time zones maintained on Time Zone Boundary Builder for our geographical mapping. You can download the geojson file in the Releases folder.

Take the file with oceans if you want to cover the whole earth. Take the one without if you don’t want to set a time zone to points that would fall into oceans (indicating coordinates are likely wrong).

The world time zones map, according to the IANA 2022g release, looks like this:

The map only includes the zone name. To use the zones, you’ll need more information (such as the Standard and Daylight Saving Times of each zone, and so on). To obtain additional details, you can process the official IANA files. If you don’t want to run that, or extract data from Wikipedia (scraping or extracting this table), you can get a consolidated CSV file from GeoPostcodes.

Uploading the Data to a Common Database

To combine the data sources, you’ll benefit from uploading them to a common framework. While it’s perfectly possible to handle the processes with widespread programming languages and libraries (e.g. Python and GeoPandas), we’ll use a database to store and query our data in this tutorial.

Our preference goes to PostgreSQL with the PostGIS extension, a leading open-source database engine with state-of-the-art geographical data processing features. It offers all the functionalities you need in this case:

• Uploading the different data sources
• Matching them using geographical attributes or other keys
• Capable of storing, querying, and exporting the results

It also natively handles time zones, including the IANA zone names, making it a great choice if you want to manage your time data in the same framework as your postcode data.

For a deeper understanding of how zip code data can be integrated into SQL databases efficiently, explore our guide on creating a zip code database in SQL.

First, set up a PostgreSQL/PostGIS database. There are numerous tutorials available for that, but we suggest this one.

After the database is set up, you can upload your different sources to it. We’re assuming in this tutorial that you’ll upload everything to a schema named timezones.

All the commands we show here can be run from any SQL client (like pgAdmin or DBeaver), but also using the psql interactive terminal.

The general psql command syntax looks like this:

psql -tA -U $username -d $database -h $host -c "#sql_command"

It’s useful to set up a password file to avoid being prompted for your password every time.

Now, it’s time to upload your files. You’ll need to unzip all the files to a common directory (/home/timezones, in this tutorial). Then, you can upload their data to your PostgreSQL database with the following commands.

Geonames.csv

First, create a table to host the data:

CREATE TABLE timezones.geonames_postcodes(
   iso char(2), 
   postcode varchar(20), 
   place varchar(180), 
   adm1_name varchar(80), 
   adm1_code varchar(20), 
   adm2_name varchar(80), 
   adm2_code varchar(20), 
   adm3_name varchar(80), 
   adm3_code varchar(20), 
   lat float, 
   lng float, 
   geo_accuracy int
);

Now we can upload the data, here using the copy command from psql:

psql -tA -U $username -d $database -h $host -c "copy timezones.geonames_postcodes FROM /home/timezones/allCountries.txt CSV HEADER DELIMITER AS E't' ;"

Alternatively, you can also use the COPY from a SQL client, and most clients offer functionalities to upload a CSV directly from the graphical interface.

IANA geojson

Leverage the ogr2ogr GDAL command to directly upload the geojson file with time zone polygons to our database:

ogr2ogr -f "PostgreSQL" -clo SCHEMA=timezones PG:"dbname=postgres user=#user" combined.json -nln timezone_polygons

Time Zone Information

You can upload the file available from GeoPostcodes’ website, GPC-TIMEZONES.csv with the following commands. First, create a table, then upload the CSV:

CREATE TABLE timezones.timezones_info(
   iso char(2), 
   country text, 
   timezone text, 
   std_offset char(6), 
   dst_offset char(6), 
   zone_abbrevation char(10), 
   zone_alt_name text
);

psql -tA -U $username -d $database -h $host -c "copy timezones.timezones_info FROM /home/timezones/GPC-TIMEZONES.csv CSV HEADER DELIMITER AS ';' ;"

Preparing Geographical Data

Once you’ve uploaded the necessary data, it’s a good idea to add a spatial index on the polygon database to speed up the spatial joins:

CREATE INDEX timezones_geom_idx ON timezones.timezone_polygons USING SPGIST(wkb_geometry);

You’ll also benefit from creating a geometry column in the Geonames table, which you can then invoke for the spatial joins:

ALTER TABLE timezones.geonames_postcodes ADD COLUMN geom geometry(point,4326);

UPDATE timezones.geonames_postcodes
SET geom = ST_Makepoint(lng,lat);

Linking Postcodes and Time Zones with Geographical Matching

After all your data is stored in the PostgreSQL database, leverage the geographical attributes in the geonames_postcodes and timezone_polygons tables to join them. Use the coordinates of every postcode and check which time zone polygon they fall in.

You can perform this with the following query, creating a new table to store your results:

CREATE TABLE timezones.postcodes_timezones AS
   SELECT g.iso, g.postcode, t.tzid
   FROM timezones.geonames_postcodes g
   JOIN timezones.timezone_polygons t ON ST_Intersects(g.geom,t.wkb_geometry);

After this process, some postcodes might be mapped to several time zones. This can happen either because time zone polygons overlap (it’s infrequent but it happens) or because the postcode is associated with several localities that fall in more than one time zone.

To properly match the time zone most associated with each postal code, run:

CREATE TABLE timezones.postcodes_tz AS
   SELECT iso, postcode, mode() WITHIN GROUP(ORDER BY tzid) AS timezone
   FROM timezones.postcodes_timezones
   GROUP BY iso, postcode;

Note that in case of a tie, mode will arbitrarily select one candidate.

Once the time zone has been identified, consider gathering extra information about it, like its Standard and Daylight Saving Times. If you want to pull in the information contained in the timezones_info table, extend the query to:

SELECT
   pt.iso,
   pt.postcode,
   mode() WITHIN GROUP(ORDER BY pt.tzid) AS timezone,
   ti.std_offset,
   ti.dst_offset,
   ti.zone_abbrevation,
   ti.zone_alt_name
FROM timezones.postcodes_timezones pt
LEFT JOIN timezones.timezones_info ti ON ti.timezone = pt.tzid
GROUP BY 1, 2, 4, 5, 6, 7;

The following screenshot shows the result, where you can see every postcode mapped to a single time zone, with high-level information about the time zone:

🔗 Download free zip code to time zone Excel sample here. For over 15 years, we’ve built a GeoPostcodes worldwide zip code database from over 1,500 sources.

Known Issues with a Postcode to Time Zone Database

The IANA time zone polygons are released shortly after any updates to the IANA rules, so the main issues you might encounter will relate to postal data.

Aside from the issue with retrieving a single time zone per postcode in the case of a tie, there are three other areas for potential hiccups to consider:

• Country coverage
• Coordinates accuracy
• Out-of-date postcode data

Country Coverage

GeoNames and OpenStreetMap provide postcodes for most countries spanning more than one time zone, except for Indonesia in the case of GeoNames. Countries with a single time zone can be mapped to time zones with country-wide rules from the timezones.timezones_info table.

Coordinates Accuracy

OpenStreetMap and GeoNames deliver generally reliable coordinates. But an issue of GeoNames is that coordinates are rounded and consequently mapped to a grid in some areas.

These coordinates will get you to the correct time zone or a neighboring one, putting you within two hours of the actual local time (accounting for Daylight Saving Time variations).

Out-of-date Postcode Data

OpenStreetMap and GeoNames do not always have the most complete and up-to-date postcode data. As a result, you could be missing postcodes. Some OpenStreetMap postcodes should just be discarded altogether, as they don’t respect the country’s postcode format.

If you can accommodate small errors (in other words, assign a time zone that’s close to correct), you can look for the closest postcode in your available data. Postcodes usually follow a hierarchical structure, so look for a postcode sharing the same leading characters. For instance, postcode 6999 should be considered closer to 6990 than 7000, and T2H 0K7 is closer to T2H 0Y0 than T2H 1K7.

Note of course that approximations can add up in cases like that. If you link a postcode to another alphabetically close postcode, that second postcode can itself have approximative coordinates.

Conclusion

While you can derive time zone information from postcodes using freely available data and software, the quality of the result is highly dependent on the data sources you use.

We’ve highlighted some common issues and explained some workarounds, but you might want to consider a commercial option if you need:

• Higher accuracy than the ±2 hours solved for in this article
• Extra information, such as the dates and times of switching to/from Daylight Saving Time
• Data that’s always up to date

GeoPostcodes maintains a worldwide database of postcodes, including up-to-date time zone information for every postcode-locality combination. When a postcode is linked to several time zones, you can access all of them and use locality to filter.

You can have a look at the Timezone product sheet or even browse the data for yourself. But don’t hesitate to reach out to us if you want to know more!

If you’re looking for ways to leverage ZIP code data for enhanced business insights and efficiency, explore how to add geocoded ZIP codes to Salesforce, ensure accuracy with address validation in Microsoft Dynamics 365, create impactful visuals by mapping ZIP codes in Tableau, and optimize logistics by calculating distances between ZIP codes using Python.

FAQ