Thread: Regarding TZ conversion

Regarding TZ conversion

From
Rajin Raj
Date:
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 

Re: Regarding TZ conversion

From
Tom Lane
Date:
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



Re: Regarding TZ conversion

From
Rajin Raj
Date:
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