Обсуждение: Regarding TZ conversion
Hi ,
What is the right approach for using AT TIME ZONE function?
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.
ovdb=> select * from pg_timezone_names where abbrev = 'IST';
name | abbrev | utc_offset | is_dst
---------------------+--------+------------+--------Asia/Calcutta | IST | 05:30:00 | f
Asia/Kolkata | IST | 05:30:00 | f
Europe/Dublin | IST | 01:00:00 | t
posix/Asia/Calcutta | IST | 05:30:00 | f
posix/Asia/Kolkata | IST | 05:30:00 | f
posix/Europe/Dublin | IST | 01:00:00 | t
posix/Eire | IST | 01:00:00 | t
Eire | IST | 01:00:00 | t
ovdb=> select * from pg_timezone_abbrevs where abbrev = 'IST';
abbrev | utc_offset | is_dst--------+------------+--------
IST | 02:00:00 | f
In my system, we receive TZ in abbrev format (3 character, like EST, PST ...).
I have tried changing the timezone_abbrevations = 'India', then it worked fine (IST is giving +5:30 offset)
So,
What is recommended, use name instead of abbrev in TZ conversion function?
Or
Change the timezone_abbrevations to 'India'?
Regards,
Rajin
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
Thanks for the clarification.
Is it advisable to modify the Default? Will it override when we apply a patch or upgrade the DB?
What about creating a new file like below and update the postgres.conf with the new name.
# New tz offset
@INCLUDE Default
@OVERRDIE
IST 19800
........................
Regards,
Rajin
On Thu, Jun 4, 2020 at 7:23 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
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