Subject: Re: Doubt on pg_timezone_names and pg_timezone_abbrevs

From: "David G. Johnston" <[email-masked]>
Date: Mon, 31 Mar 2025 20:58:11 -0700

On Mon, Mar 31, 2025 at 8:39 PM Jayadevan M <[email-masked]>
wrote:

> Hello PG members,
> I used 'IST'  in a query like this - * (timestamp_hour) at time zone
> 'IST' time_ist *and did not get the expected output - timestamp in Indian
> Standard Time. So I queried the 2 views that provide timezone info and did
> not really understand the abbrev column.
> select name, abbrev, utc_offset  from pg_timezone_names  where abbrev =
> 'IST'  ;
>

Since the S and T are non-location specific you get 26 different timezone
abbreviations to choose from. That wasn't enough for the world.  So IST is
non-unique; and for historical reasons Ireland (Eire, which contains
Dublin) is given default priority.

>      name      | abbrev | utc_offset
> ---------------+--------+------------
>  Eire          | IST    | 01:00:00
>  Asia/Kolkata  | IST    | 05:30:00
>  Asia/Calcutta | IST    | 05:30:00
>  Europe/Dublin | IST    | 01:00:00
>

Suggest you adapt to using ISO names (the name column above) for timezones;
which are long enough and location-specific enough to be unique.  In your
case, pick your preferred spelling of Calcutta I suppose.

There is a way to get a different interpretation for IST to be recognized
but I'd have to find it or wait for others to chime in.

David J.

---

From: Jayadevan M <[email-masked]>
Date: Tue, 1 Apr 2025 12:02:43 +0530

On Tue, Apr 1, 2025 at 9:28 AM David G. Johnston <[email-masked]>
wrote:

>> 'IST' time_ist *and did not get the expected output - timestamp in
>> Indian Standard Time. So I queried the 2 views that provide timezone info
>> and did not really understand the abbrev column.
> Suggest you adapt to using ISO names (the name column above) for
> timezones; which are long enough and location-specific enough to be
> unique.  In your case, pick your preferred spelling of Calcutta I suppose.
>
> Thank you. I used Calcutta.
Regards,
Jayadevan