Group world cities by timezone

In the previous post, I released pg-tzf, a PostgreSQL extension providing microsecond-level GPS coordinates to time zone queries.

In this post, I will show you how to group world cities by timezone.

Data source: https://github.com/lutangar/cities.json

1
git clone https://github.com/lutangar/cities.json

Take a look at the first 30 lines of the file:

1
head -n 30 cities.json/cities.json

Output as:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
[
  {
    "name": "El Tarter",
    "lat": "42.57952",
    "lng": "1.65362",
    "country": "AD",
    "admin1": "02",
    "admin2": ""
  },
  {
    "name": "Sant Julià de Lòria",
    "lat": "42.46372",
    "lng": "1.49129",
    "country": "AD",
    "admin1": "06",
    "admin2": ""
  },
  {
    "name": "Pas de la Casa",
    "lat": "42.54277",
    "lng": "1.73361",
    "country": "AD",
    "admin1": "03",
    "admin2": ""
  },
  {
    "name": "Ordino",
    "lat": "42.55623",
    "lng": "1.53319",
    "country": "AD",

Save all cities to PG first:

1
2
3
4
5
6
7
8
# Create a table
psql -d postgres -c "CREATE TABLE cities (name TEXT, lng DOUBLE PRECISION, lat DOUBLE PRECISION, country TEXT, admin1 TEXT, admin2 TEXT);"

# Convert the JSON file to CSV
jq -r '.[] | [.name, .lng, .lat, .country, .admin1, .admin2] | @csv' cities.json/cities.json > cities.csv

# Copy the CSV file to the table
psql -d postgres -c "\copy cities(name, lng, lat, country, admin1, admin2) FROM './cities.csv' WITH (FORMAT csv);"

Take a look at the first 5 records of the table:

1
psql -d postgres -c "SELECT name, lng, lat, country FROM cities LIMIT 5;"

Output as:

1
2
3
4
5
6
7
8
        name         |   lng   |   lat    | country
---------------------+---------+----------+---------
 El Tarter           | 1.65362 | 42.57952 | AD
 Sant Julià de Lòria | 1.49129 | 42.46372 | AD
 Pas de la Casa      | 1.73361 | 42.54277 | AD
 Ordino              | 1.53319 | 42.55623 | AD
 les Escaldes        | 1.53414 | 42.50729 | AD
(5 rows)

Now, we can group the cities by timezone.

Setup extension first:

1
psql -d postgres -c "CREATE EXTENSION IF NOT EXISTS tzf;"

Group the cities by timezone:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
-- Enter shell via `psql -d postgres`
SELECT
  tzf_tzname(lng, lat) AS timezone,
  COUNT(*) AS count
FROM
  cities
GROUP BY
  timezone
ORDER BY
  count DESC;

Output as:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
            timezone            | count
--------------------------------+-------
 Europe/Rome                    |  9946
 Europe/Paris                   |  8881
 America/New_York               |  7692
 Europe/Berlin                  |  7404
 America/Mexico_City            |  7402
 Europe/Madrid                  |  7033
 America/Chicago                |  5398
 Asia/Shanghai                  |  4704
 Europe/London                  |  4611
 Europe/Bucharest               |  4570
 Asia/Manila                    |  4502
 Asia/Kolkata                   |  4424
 Africa/Abidjan                 |  3773
 Europe/Warsaw                  |  2942
 Europe/Moscow                  |  2801
 Europe/Istanbul                |  2480
 Europe/Vienna                  |  2260
 Asia/Tokyo                     |  2113
 Europe/Kyiv                    |  1844
 America/Los_Angeles            |  1817
 Europe/Brussels                |  1735
 America/Lima                   |  1681
 Europe/Amsterdam               |  1440

Now, let’s find cities where the local time is between 8 AM and 12 PM:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- Please note that new timezone name may be added with new tzdb release and so
-- as boundary data used by this extension. In below example, I have to ignore 
-- unknown timezone name "America/Coyhaique" which released in tzdb 2025b.
WITH valid_timezones AS (
    SELECT
        c.name,
        c.country,
        c.lng,
        c.lat,
        tzf_tzname(c.lng, c.lat) AS timezone,
        t.utc_offset
    FROM
        cities c
        JOIN pg_timezone_names t ON tzf_tzname(c.lng, c.lat) = t.name
)
SELECT
    name,
    country,
    timezone,
    (CURRENT_TIMESTAMP + utc_offset) :: time AS local_time
FROM
    valid_timezones
WHERE
    (CURRENT_TIMESTAMP + utc_offset) :: time BETWEEN '08:00:00'
    AND '12:00:00'
ORDER BY
    local_time DESC
LIMIT
    10;

It could output like this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
        name         | country |    timezone    |   local_time    
---------------------+---------+----------------+-----------------
 Xaibe               | BZ      | America/Belize | 11:32:36.139515
 Unitedville         | BZ      | America/Belize | 11:32:36.139515
 Trial Farm          | BZ      | America/Belize | 11:32:36.139515
 Teakettle Village   | BZ      | America/Belize | 11:32:36.139515
 Spanish Lookout     | BZ      | America/Belize | 11:32:36.139515
 Silk Grass          | BZ      | America/Belize | 11:32:36.139515
 Shipyard            | BZ      | America/Belize | 11:32:36.139515
 Seine Bight Village | BZ      | America/Belize | 11:32:36.139515
 Sarteneja           | BZ      | America/Belize | 11:32:36.139515
 Yo Creek            | BZ      | America/Belize | 11:32:36.139515
(10 rows)