In the previous post, I released
tzf-pg, 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)
|