public inbox for [email protected]
help / color / mirror / Atom feedRe: Doubt on pg_timezone_names and pg_timezone_abbrevs
2+ messages / 2 participants
[nested] [flat]
* Re: Doubt on pg_timezone_names and pg_timezone_abbrevs
@ 2025-04-01 03:58 David G. Johnston <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: David G. Johnston @ 2025-04-01 03:58 UTC (permalink / raw)
To: Jayadevan M <[email protected]>; +Cc: pgsql-general
On Mon, Mar 31, 2025 at 8:39 PM Jayadevan M <[email protected]>
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Doubt on pg_timezone_names and pg_timezone_abbrevs
@ 2025-04-01 06:32 Jayadevan M <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Jayadevan M @ 2025-04-01 06:32 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: pgsql-general
On Tue, Apr 1, 2025 at 9:28 AM David G. Johnston <[email protected]>
wrote:
> On Mon, Mar 31, 2025 at 8:39 PM Jayadevan M <[email protected]>
> 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-04-01 06:32 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-04-01 03:58 Re: Doubt on pg_timezone_names and pg_timezone_abbrevs David G. Johnston <[email protected]>
2025-04-01 06:32 ` Jayadevan M <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox