MIME-Version: 1.0
From: Jayadevan M <maymala.jayadevan@gmail.com>
Date: Tue, 1 Apr 2025 09:08:50 +0530
Message-ID: <CAFS1N4go0PyXzR=krVN1EqdBx+T_Wv+P736KxDyZ8ZGuCfi8Rw@mail.gmail.com>
Subject: Doubt on pg_timezone_names and pg_timezone_abbrevs
To: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000008f1b00631af47c7"
Archived-At: <https://www.postgresql.org/message-id/CAFS1N4go0PyXzR%3DkrVN1EqdBx%2BT_Wv%2BP736KxDyZ8ZGuCfi8Rw%40mail.gmail.com>
Precedence: bulk

--00000000000008f1b00631af47c7
Content-Type: text/plain; charset="UTF-8"

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'  ;
     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


select * from pg_timezone_abbrevs where abbrev = 'IST'  ;
 abbrev | utc_offset | is_dst
--------+------------+--------
 IST    | 02:00:00   | f

This is PostgreSQL 13.15 on AWS RDS. We have the same abbrev for 4
timezones in pg_timezone_names.
Regards,
Jayadevan

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

<div dir=3D"ltr">Hello PG members,<div>I used &#39;IST&#39;=C2=A0 in a quer=
y 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 understand the abbrev column.=C2=A0</div><div>select name, abbre=
v, utc_offset =C2=A0from pg_timezone_names =C2=A0where abbrev =3D &#39;IST&=
#39; =C2=A0;<br>=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:00<br><b=
r><br>select * from pg_timezone_abbrevs where abbrev =3D &#39;IST&#39; =C2=
=A0;<br>=C2=A0abbrev | utc_offset | is_dst <br>--------+------------+------=
--<br>=C2=A0IST =C2=A0 =C2=A0| 02:00:00 =C2=A0 | f</div><div><br></div><div=
>This is=C2=A0PostgreSQL 13.15 on AWS RDS. We have the same abbrev for 4 ti=
mezones in pg_timezone_names.</div><div>Regards,</div><div>Jayadevan</div><=
div><br></div><div><br></div><div><br></div></div>

--00000000000008f1b00631af47c7--