Re: Regarding TZ conversion

Rajin Raj <rajin.raj@opsveda.com> writes:
> Option 1: <some_date with tz> AT TIME ZONE 'IST'
> Option 2: <some_date with tz> AT TIME ZONE 'Asia/Kolkata'
> In the first option, I get +2:00:00 offset (when *timezone_abbrevations =
> 'Default'*) and for option 2 , +5:30 offset.

> I can see multiple entries for IST in pg_timezone_names with
> different utc_offset, but in pg_timezone_abbrev there is one entry. I guess
> AT TIME ZONE function using the offset shown in pg_timezone_abbrev.

No.  If you use an abbreviation rather than a spelled-out zone name,
you get whatever the timezone_abbrevations file says, which by default
is

$ grep IST .../postgresql/share/timezonesets/Default
# CONFLICT! IST is not unique
# - IST: Irish Standard Time (Europe)
# - IST: Indian Standard Time (Asia)
IST      7200    # Israel Standard Time

If that's not what you want, change it.  See

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

and also

https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES

            regards, tom lane