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

Timing matters. Whether you want to remind customers about reservations, run a survey, or send marketing messages, contacting people at the optimal time is a key to business success. In the case of call centers, timing can even be critical, when you really need live interaction with the recipient.

But knowing the proper time to call can be tricky when you’re working with a large user base. Your recipients may be spread over a range of time zones, and it’s not always straightforward to gather that data from standard user information. Some large countries (e.g., USA, Canada, Mexico, Brazil, Russia) span numerous time zones; you’ll need more than just a country of residence to determine the time zone of their residents.

In this article, we’ll see how you can infer time zones from international postcodes. Zip codes (also known as postal codes or postcodes) are an element of standard address formatting. They’re also frequently included in user information, making zip codes a good candidate for determining time zones.

What Is Zip Code to Time Zone Mapping?

Zip code to time zone mapping is the process of associating a time zone to its correct postal code. The end goal of this is to facilitate reaching out to any contact at a suitable time.

This requires access to:

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

When mapping zip codes to time zones across a single country, it’s usually feasible to work with a set of time zone rules (e.g., California is in time zone America/Los_Angeles, while New York is in time zone America/New_York). However, this can already require some multi-level processing in order to build accurate mapping. For instance, states and even counties in the USA can include more than one time zone. As a result, you would need to find a source that maps zip codes to states/counties, then assign time zones to states/counties and then encode exceptions.

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

Assigning Time Zones to Zip Codes

Let’s dive into the practicalities of assigning time zones to zip codes. In this section, we’ll talk about where to find the base data (the postcodes and time zones we’ll work with), how to upload the data to a common framework, and finally how to use both datasets to assign a time zone to every postcode.

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.

Sourcing Time Zone Data

The most widely used time zone data is published by the Internet Assigned Numbers Authority. It includes a set of rules and code that anyone can process, not only to capture the different time zones, but also to access the past and future time switches from Standard Time (sometimes referred to as “winter time”) to Daylight Saving Time (“summer time”) in relevant time zones.

For the sake of our geographical mapping, we’ll use a map of the time zones as maintained on Time Zone Boundary Builder. This project releases high quality maps of the official IANA time zones. 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 assign 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:

Opposite to the maps we’re used to, the standard map if the world gets distorted the closer you get to the poles, and the bigger things look relative to their actual size (IANA World time zones map)

However, the map only includes the zone name. In order to use the zones, you’ll need more information (such as the Standard and Daylight Saving Times of each time zone, and so on). To obtain additional details like that, you can process the official IANA time zone 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 postcodes data.

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

Once you have the database 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’s 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

Now that 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 a locality that falls in more than one time zone.

To properly match the time zone most associated to 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:

Known Issues with a Postcode to Time Zone Database

The IANA time zone polygons are released shortly after any updates to the IANA time zone 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 produce postcodes for the vast majority of countries spanning multiple time zones (the main exception being Indonesia for GeoNames). The other countries, which are covered by a single time zone, can be mapped to time zones with country-wide rules, taken from the timezones.timezones_info table.

Coordinates Accuracy

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

These approximate coordinates will generally take you to the correct time zone or at least a neighboring time zone, so you’ll end up within two hours of the actual local time (taking variations about Daylight Saving Times into account).

Out-of-date Postcode Data

Both 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 results 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!