A timezone describes a region of the globe with a uniform standard time. PostgreSQL supports various temporal data types that assist us in storing, retrieving, or manipulating the DateTime values with or without a timezone. Therefore, while working with these temporal data types you may need to get the list of Postgres-supported time zones. Postgres provides various methods to achieve this task.
This write-up will present a profound understanding of getting the list of Postgres-supported Timezones using various methods.
How to Get/Return a List of PostgreSQL-Supported Timezones?
In Postgres, you can use one of the following methods to get the list of Postgres-supported timezones:
- Method 1: Using the “pg_timezone_names” View
- Method 2: Using the pg_timezone_abbrevs View
- Method 3: Using the pg_timezone_abbrevs() Function
Let’s start with the “pg_timezone_names” View.
How to Get Postgres-Supported Timezones Using the “pg_timezone_names” View?
Use the “pg_timezone_names” view with the SELECT query to populate the list of Postgres-supported timezones:
SELECT * FROM pg_timezone_names;
The “pg_timezone_names” successfully retrieves the list of Postgres-supported timezones.
How to Get Postgres-Supported Timezones Using the “pg_timezone_abbrevs” View?
Run the “SELECT *” query along with the “pg_timezone_abbrevs” view to get the list of Postgres-supported timezones:
SELECT * FROM pg_timezone_abbrevs;
The output demonstrates that the “pg_timezone_abbrevs” view retrieves the abbreviated time zones along with the utc_offset.
How to Get Postgres-Supported Timezones Using the “pg_timezone_abbrevs()” Function?
The pg_timezone_abbrevs() function can also be used as an alternative to the pg_timezone_abbrevs view to get the PostgreSQL-supported timezones:
SELECT * FROM pg_timezone_abbrevs();
The output shows that the stated function retrieves the same output as the pg_timezone_abbrevs view.
Conclusion
In Postgres, the pg_timezone_abbrevs() function, the “pg_timezone_names” view, and the pg_timezone_abbrevs view are used to get the list of PostgreSQL-supported timezones. All these approaches are utilized with the help of the SELECT statement. This blog has illustrated various methods to get the list of PostgreSQL-supported timezones.
