Thread: Doubt on pg_timezone_names and pg_timezone_abbrevs

Doubt on pg_timezone_names and pg_timezone_abbrevs

From

Jayadevan M

Date:

01 April, 06:38:50

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.

This is PostgreSQL 13.15 on AWS RDS. We have the same abbrev for 4 timezones in pg_timezone_names.

Regards,

Jayadevan

Re: Doubt on pg_timezone_names and pg_timezone_abbrevs

From

"David G. Johnston"

Date:

01 April, 06:58:11

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.

There is a way to get a different interpretation for IST to be recognized but I'd have to find it or wait for others to chime in.

David J.

Re: Doubt on pg_timezone_names and pg_timezone_abbrevs

From

Tom Lane

Date:

01 April, 07:06:34

Jayadevan M <maymala.jayadevan@gmail.com> writes:
> 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.

I think IST defaults to 'Israel Standard Time', a/k/a Asia/Jerusalem,
a/k/a UTC+2.  To get it to mean Indian Standard Time a/k/a Asia/Calcutta
you need

set timezone_abbreviations TO 'India';

(or more likely, adjust that in your installation's postgresql.conf).

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

            regards, tom lane

Re: Doubt on pg_timezone_names and pg_timezone_abbrevs

From

Jayadevan M

Date:

01 April, 09:32:43

On Tue, Apr 1, 2025 at 9:28 AM David G. Johnston <david.g.johnston@gmail.com> wrote:

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.

Thank you. I used Calcutta.

Regards,

Jayadevan