MIME-Version: 1.0
References: <CAFS1N4go0PyXzR=krVN1EqdBx+T_Wv+P736KxDyZ8ZGuCfi8Rw@mail.gmail.com>
 <CAKFQuwawH6KnKLEU+h=vcsBE5UFkZ+S1sLp_tk6aVDPg3G5XoQ@mail.gmail.com>
In-Reply-To: <CAKFQuwawH6KnKLEU+h=vcsBE5UFkZ+S1sLp_tk6aVDPg3G5XoQ@mail.gmail.com>
From: Jayadevan M <maymala.jayadevan@gmail.com>
Date: Tue, 1 Apr 2025 12:02:43 +0530
Message-ID: <CAFS1N4hHnECpW6Rn7ZvuY9NXQp1uViJvAczBtzqE2pVPey9Enw@mail.gmail.com>
Subject: Re: Doubt on pg_timezone_names and pg_timezone_abbrevs
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000e8858a0631b1b4ab"
Archived-At: <https://www.postgresql.org/message-id/CAFS1N4hHnECpW6Rn7ZvuY9NXQp1uViJvAczBtzqE2pVPey9Enw%40mail.gmail.com>
Precedence: bulk

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

On Tue, Apr 1, 2025 at 9:28=E2=80=AFAM David G. Johnston <david.g.johnston@=
gmail.com>
wrote:

> On Mon, Mar 31, 2025 at 8:39=E2=80=AFPM Jayadevan M <maymala.jayadevan@gm=
ail.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 inf=
o
>> and did 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 i=
s
> 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=
.
>
> Thank you. I used Calcutta.
Regards,
Jayadevan

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

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote g=
mail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Tue, Apr 1, =
2025 at 9:28=E2=80=AFAM David G. Johnston &lt;<a href=3D"mailto:david.g.joh=
nston@gmail.com">david.g.johnston@gmail.com</a>&gt; wrote:<br></div><blockq=
uote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1p=
x solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr=
"><div style=3D"font-family:arial,helvetica,sans-serif"><span style=3D"font=
-family:Arial,Helvetica,sans-serif">On Mon, Mar 31, 2025 at 8:39=E2=80=AFPM=
 Jayadevan M &lt;<a href=3D"mailto:maymala.jayadevan@gmail.com" target=3D"_=
blank">maymala.jayadevan@gmail.com</a>&gt; wrote:</span></div></div><div cl=
ass=3D"gmail_quote"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0=
px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div =
dir=3D"ltr">Hello PG members,<div>I used &#39;IST&#39;=C2=A0 in a query lik=
e 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 Stand=
ard Time. So I queried the 2 views that provide timezone info and did not r=
eally understand the abbrev column.=C2=A0</div><div>select name, abbrev, ut=
c_offset =C2=A0from pg_timezone_names =C2=A0where abbrev =3D &#39;IST&#39; =
=C2=A0;<br></div></div></blockquote><div><br></div><div><div style=3D"font-=
family:arial,helvetica,sans-serif">Since the S and T are non-location speci=
fic you get 26 different timezone abbreviations to choose from. That wasn&#=
39;t enough for the world.=C2=A0 So IST is non-unique; and for historical r=
easons Ireland (Eire, which contains Dublin) is given default priority.</di=
v></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/Calcutta | IST =
=C2=A0 =C2=A0| 05:30:00<br>=C2=A0Europe/Dublin | IST =C2=A0 =C2=A0| 01:00:0=
0<br></div></div></blockquote><div><br></div><div style=3D"font-family:aria=
l,helvetica,sans-serif">Suggest you adapt to using ISO names (the name colu=
mn above) for timezones; which are long enough and location-specific enough=
 to be unique.=C2=A0 In your case, pick your preferred spelling of Calcutta=
 I suppose.</div><div style=3D"font-family:arial,helvetica,sans-serif"><br>=
</div></div></div></blockquote><div>Thank you. I used Calcutta.</div><div>R=
egards,</div><div>Jayadevan=C2=A0</div></div></div>

--000000000000e8858a0631b1b4ab--