Subject: Doubt on pg_timezone_names and pg_timezone_abbrevs

From: Jayadevan M <[email-masked]>
Date: Tue, 1 Apr 2025 09:08:50 +0530

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

---

From: Tom Lane <[email-masked]>
Date: Tue, 01 Apr 2025 00:06:34 -0400

Jayadevan M <[email-masked]> writes:
> Standard Time.

I think IST defaults to 'Israel Standard Time', a/k/a Asia/Jerusalem,
a/k/a UTC+2.  To get it to mean Indian Standard Time a/k/a Asia/Calcutta
you need

set timezone_abbreviations TO 'India';

(or more likely, adjust that in your installation's postgresql.conf).

See https://www.postgresql.org/docs/current/datetime-config-files.html

			regards, tom lane