Awesome

BigQuery Public Dataset for Timezone Data

An public mirror of the tzdb (timezone database) from IANA and the timezone boundaries within BigQuery.

This is being offered as a "best effort" replication to support anybody who would need this information within BigQuery .

Releases

The data will be mirrored into the BigQuery datasets on a best-effort basis; for example the availability of new version's within this dataset could lag behind the releases found from both sources.

What is BigQuery?

Bigquery is a managed data warehouse from the Google Cloud platform; the official documentation can be found here . There can be costs from Google Cloud when consuming this data; it is advisable to checkout the pricing page before consuming.

Contents

Release Version Identifiers

The release version identifiers are based on the IANA timezone release identifiers, today this is in the format of YYYYx, for example 2023d.

EU vs US

All data is replicated into both the EU and US Multi Regions. The datasets all have a suffix to represent which region the data is stored within. Both regions contain all global data and are provided as a convenience to the consumer.

Latest Version

Two datasets contain the most recent released version for each table. It could be that the difference sources have a different latest version as each source is updated independently. Each table within the latest datasets has a column showing the current version.

There are some more technical tables (such as raw JSON tables) that are not available within the latest version and need to be sourced per version; this is to ensure that the latest tables and content are stable (where-as source JSON might change.

Version Datasets

As an alternative to the latest version datasets, previous versions are kept as a historical record and to ensure you can migrate to the next version at your own pace. The sources have many years of history; however we are starting from the 2023 releases.

Detailed Contents

Here is a list of the tables currently available. Unless otherwise mentioned the schema of these tables is the same across versions. For more detailed descriptions of the contents I would suggest reading the documentation from the source directly.

Examples

Lookup a timezone from a set of coordinates

SELECT
    timezone_id
FROM
    `tz-data.latest_EU.timezones`
WHERE
    ST_WITHIN(
        ST_GEOGPOINT(
            /* longitude */ -0.13948559859956436,
            /* latitude */ 51.5029659891868
        ),
        geometry
    );
LIMIT 1

Return Value:

Get timezones for multiple places

Using the standard list, if a place is within the ocean it will not return a timezone.

WITH places AS (
  SELECT "Buckingham Palace" AS place,51.500833 AS lat,-0.141944 AS long,
  UNION ALL SELECT "Null Island" AS place,0.0 AS lat,0.0 AS long,
  UNION ALL SELECT "Statue of Liberty" AS place,40.689167 AS lat,-74.044444 AS long,
  UNION ALL SELECT "Sydney Opera House" AS place,-33.858611 AS lat,151.214167 AS long,
  UNION ALL SELECT "Taj Mahal" AS place,27.175 AS lat,78.041944 AS long
)
SELECT
  p.place,
  p.lat,
  p.long,
  t.timezone_id,
  CURRENT_TIMESTAMP AS utc_time,
  DATETIME(CURRENT_TIMESTAMP,t.timezone_id) AS local_time
FROM
  places p
  LEFT JOIN `tz-data.latest_EU.timezones` t
    ON ST_WITHIN(
        ST_GEOGPOINT(
          p.long,
          p.lat
        ),
        t.geometry
    )
ORDER BY
  p.place ASC

Return Value:

Get timezones for multiple places, including the oceans

Here NULL island returns a timezone, as it within the ocean.

WITH places AS (
  SELECT "Buckingham Palace" AS place,51.500833 AS lat,-0.141944 AS long,
  UNION ALL SELECT "Null Island" AS place,0.0 AS lat,0.0 AS long,
  UNION ALL SELECT "Statue of Liberty" AS place,40.689167 AS lat,-74.044444 AS long,
  UNION ALL SELECT "Sydney Opera House" AS place,-33.858611 AS lat,151.214167 AS long,
  UNION ALL SELECT "Taj Mahal" AS place,27.175 AS lat,78.041944 AS long
)
SELECT
  p.place,
  p.lat,
  p.long,
  t.timezone_id,
  CURRENT_TIMESTAMP AS utc_time,
  DATETIME(CURRENT_TIMESTAMP,t.timezone_id) AS local_time
FROM
  places p
  LEFT JOIN `tz-data.latest_EU.timezones` t
    ON ST_WITHIN(
        ST_GEOGPOINT(
          p.long,
          p.lat
        ),
        t.geometry
    )
ORDER BY
  p.place ASC

Return Value:

Licenses

• timezone-boundary data is licensed under the Open Data Commons Open Database License (ODbL).

• IANA timezone database license can be found here