Query IANA Time Zone Names via ST_TimeZone for Point Geometry - PolarDB

Returns the time zone for a given point location.

Syntax

cstring ST_TimeZone(geometry point, boolean timezoneId, integer timezonePrecision);
cstring ST_TimeZone(geography point, boolean timezoneId, integer timezonePrecision);

Parameters

Parameter	Description
`point`	The geometry or geography object. Must be a point with the WGS84 spatial reference system (`srid=4326`).
`timezoneId`	Specifies whether to return the time zone name. Valid values: `true` (default): returns the time zone name, such as `Asia/Shanghai`. `false`: returns the time zone in GMT format, such as `Etc/GMT+8`.
`timezonePrecision`	The precision of the time zone boundary lookup. Valid only when `timezoneId` is `true`. Valid values: `16` (default): precision of 0.0055 degrees (~500 m). `21`: precision of 0.00017 degrees (~20 m).

Return value

Returns a cstring containing the time zone string.

Description

ST_TimeZone returns the time zone at a given geographic point. Time zone boundary data is sourced from the Timezone Boundary Builder project.

Examples

Return the time zone name for a geometry point:

SELECT ST_TimeZone('SRID=4326;POINT(121 37)'::geometry);
----
Asia/Shanghai

Return the time zone name for a geography point:

SELECT ST_TimeZone('POINT(60 37)'::geography);
----
Asia/Tehran

Return the time zone in GMT format:

-- Set timezoneId to false to return the GMT format instead of the name format.
SELECT ST_TimeZone('SRID=4326;POINT(121 37)'::geometry, false);
----
Etc/GMT+8

Return the time zone with higher precision:

-- Set timezonePrecision to 21 for ~20 m precision instead of the default ~500 m.
SELECT ST_TimeZone('SRID=4326;POINT(-121 37)'::geometry, true, 21);
----
America/Los_Angeles

Handle a point with a longitude outside the valid range:

-- The specified longitude exceeds the valid range (> 180°).
SELECT ST_TimeZone(('SRID=4326;POINT(400 37)'::geometry));
----
Europe/Istanbul