public inbox for [email protected]
help / color / mirror / Atom feedDoubt on pg_timezone_names and pg_timezone_abbrevs
2+ messages / 2 participants
[nested] [flat]
* Doubt on pg_timezone_names and pg_timezone_abbrevs
@ 2025-04-01 03:38 Jayadevan M <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: Jayadevan M @ 2025-04-01 03:38 UTC (permalink / raw)
To: pgsql-general
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' ;
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
select * from pg_timezone_abbrevs where abbrev = 'IST' ;
abbrev | utc_offset | is_dst
--------+------------+--------
IST | 02:00:00 | f
This is PostgreSQL 13.15 on AWS RDS. We have the same abbrev for 4
timezones in pg_timezone_names.
Regards,
Jayadevan
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Doubt on pg_timezone_names and pg_timezone_abbrevs
@ 2025-04-01 04:06 Tom Lane <[email protected]>
parent: Jayadevan M <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2025-04-01 04:06 UTC (permalink / raw)
To: Jayadevan M <[email protected]>; +Cc: pgsql-general
Jayadevan M <[email protected]> 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-04-01 04:06 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:38 Doubt on pg_timezone_names and pg_timezone_abbrevs Jayadevan M <[email protected]>
2025-04-01 04:06 ` Tom Lane <[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