From: | Rajin Raj <rajin(dot)raj(at)opsveda(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Regarding TZ conversion |
Date: | 2020-06-04 09:45:01 |
Message-ID: | CAOasRJYKMVkqsnsML4cS6T=BVneztJ1RbOQm43JmgcgiS1Vj=A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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 *
From | Date | Subject | |
---|---|---|---|
Next Message | movead.li@highgo.ca | 2020-06-04 09:49:25 | Re: proposal - function string_to_table |
Previous Message | Jerome Wagner | 2020-06-04 09:37:54 | Re: question regarding copyData containers |