MIME-Version: 1.0
References: <CAFS1N4go0PyXzR=krVN1EqdBx+T_Wv+P736KxDyZ8ZGuCfi8Rw@mail.gmail.com>
In-Reply-To: <CAFS1N4go0PyXzR=krVN1EqdBx+T_Wv+P736KxDyZ8ZGuCfi8Rw@mail.gmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Mon, 31 Mar 2025 20:58:11 -0700
Message-ID: <CAKFQuwawH6KnKLEU+h=vcsBE5UFkZ+S1sLp_tk6aVDPg3G5XoQ@mail.gmail.com>
Subject: Re: Doubt on pg_timezone_names and pg_timezone_abbrevs
To: Jayadevan M <maymala.jayadevan@gmail.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000ca13b50631af8d22"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwawH6KnKLEU%2Bh%3DvcsBE5UFkZ%2BS1sLp_tk6aVDPg3G5XoQ%40mail.gmail.com>
Precedence: bulk

--000000000000ca13b50631af8d22
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Mar 31, 2025 at 8:39=E2=80=AFPM Jayadevan M <maymala.jayadevan@gmai=
l.com>
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 di=
d
> not really understand the abbrev column.
> select name, abbrev, utc_offset  from pg_timezone_names  where abbrev =3D
> '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.

--000000000000ca13b50631af8d22
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div class=3D"gmail_default" style=3D"fon=
t-family:arial,helvetica,sans-serif"><span style=3D"font-family:Arial,Helve=
tica,sans-serif">On Mon, Mar 31, 2025 at 8:39=E2=80=AFPM Jayadevan M &lt;<a=
 href=3D"mailto:maymala.jayadevan@gmail.com">maymala.jayadevan@gmail.com</a=
>&gt; wrote:</span></div></div><div class=3D"gmail_quote gmail_quote_contai=
ner"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bo=
rder-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr">Hel=
lo PG members,<div>I used &#39;IST&#39;=C2=A0 in a query like this -=C2=A0<=
b><i>=C2=A0(timestamp_hour) at time zone &#39;IST&#39; time_ist </i></b>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 understan=
d the abbrev column.=C2=A0</div><div>select name, abbrev, utc_offset =C2=A0=
from pg_timezone_names =C2=A0where abbrev =3D &#39;IST&#39; =C2=A0;<br></di=
v></div></blockquote><div><br></div><div><div class=3D"gmail_default" style=
=3D"font-family:arial,helvetica,sans-serif">Since the S and T are non-locat=
ion specific you get 26 different timezone abbreviations to choose from. Th=
at wasn&#39;t enough for the world.=C2=A0 So IST is non-unique; and for his=
torical reasons Ireland (Eire, which contains Dublin) is given default prio=
rity.</div></div><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=
=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding=
-left:1ex"><div dir=3D"ltr"><div>=C2=A0 =C2=A0 =C2=A0name =C2=A0 =C2=A0 =C2=
=A0| abbrev | utc_offset <br>---------------+--------+------------<br>=C2=
=A0Eire =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| IST =C2=A0 =C2=A0| 01:00:00<br>=
=C2=A0Asia/Kolkata =C2=A0| IST =C2=A0 =C2=A0| 05:30:00<br>=C2=A0Asia/Calcut=
ta | IST =C2=A0 =C2=A0| 05:30:00<br>=C2=A0Europe/Dublin | IST =C2=A0 =C2=A0=
| 01:00:00<br></div></div></blockquote><div><br></div><div class=3D"gmail_d=
efault" style=3D"font-family:arial,helvetica,sans-serif">Suggest you adapt =
to using ISO names (the name column above) for timezones; which are long en=
ough and location-specific enough to be unique.=C2=A0 In your case, pick yo=
ur preferred spelling of Calcutta I suppose.</div><div class=3D"gmail_defau=
lt" style=3D"font-family:arial,helvetica,sans-serif"><br></div><div class=
=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif">There i=
s a way to get a different interpretation for IST to be recognized but I=
9;d have to find it or wait for others to chime in.</div><div class=3D"gmai=
l_default" style=3D"font-family:arial,helvetica,sans-serif"><br></div><div =
class=3D"gmail_default" style=3D"font-family:arial,helvetica,sans-serif">Da=
vid J.</div></div></div>

--000000000000ca13b50631af8d22--