Add a timezone(timestamp(p) with time zone) -> varchar extraction function to get IANA timezone ID

[stephen-zhao]

Request

Similar to the current_timezone() function which returns the timezone of the current session, it would be nice to generalize this to allow extraction of timezone info from any provided timestamp(p) with time zone. Currently there are already functions to get timezone offsets (extract(TIMEZONE_HOUR from x) and extract(TIMEZONE_MINUTE from x)), but this loses information about the actual timezone. A lossless extraction function that would return the IANA timezone ID would be ideal.

Workaround

You can cast the timestamp(p) with time zone to a string, and manually extract the suffix.

[hashhar]

cc: @martint

[ebyhr]

The argument for EXTRACT is defined in SQL standard:

<time zone field> ::=
    TIMEZONE_HOUR
  | TIMEZONE_MINUTE

Is format_datetime function sufficient in your use case?

SELECT format_datetime(TIMESTAMP '2001-01-09 13:04:05.321 Asia/Tokyo', 'ZZZ');
   _col0
------------
 Asia/Tokyo

[martint]

Apologies for the delay. This is a reasonable request. To keep alignment with existing extraction functions (https://trino.io/docs/current/functions/datetime.html#convenience-extraction-functions), we should define it as:

timezone(<timestamp(p) with time zone>) -> varchar
timezone(<time(p) with time zone>) -> varchar