Re: Doubt on pg_timezone_names and pg_timezone_abbrevs

On Mon, Mar 31, 2025 at 8:39 PM Jayadevan M <maymala.jayadevan@gmail.com> wrote:

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'  ;

Since the S and T are non-location specific you get 26 different timezone abbreviations to choose from. That wasn't enough for the world.  So IST is non-unique; and for historical reasons Ireland (Eire, which contains Dublin) is given default priority.

 

     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

Suggest you adapt to using ISO names (the name column above) for timezones; which are long enough and location-specific enough to be unique.  In your case, pick your preferred spelling of Calcutta I suppose.