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 each postal code of a dataset.
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 individual time zone rules, but use a more convenient method for international data, focusing on geographical matching between postcodes and mapped 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
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:
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
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.
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.
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
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,
modewill 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
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
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.
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.
How is a zip code/time zone database useful?
When you want to remind customers about reservations, run a survey, or send marketing messages, contacting people at the optimal time is key to business success. In the case of call centers, timing can even be critical, when you really need live interaction with the recipient.
What information does a zip code/time zone database contain?
A zip code/time zone database contains information about the time zone associated with each zip code. It provides data that helps identify the specific time zone a zip code falls within, enabling accurate time-based calculations and scheduling.
These databases are utilized in various applications requiring time zone conversions, such as scheduling systems, online services, and international communication platforms.
How frequently is the zip code/time zone database updated?
Every time there is a change in the time zones or each time we do a country update we may also correct some administrative division assignations, which can trigger changes to the time zones some localities are associated with, but that’s not really common.