PostgreSQL Set DB Timezone

If you plan to work with date and time data in PostgreSQL, you’ll want to ensure that you’ve set the correct time zone for your location. Fortunately, it’s easy to make this change using the psql command-line interface. In this article, we’ll explain how to connect to PostgreSQL and set the time zone.

It’s easy to connect to PostgreSQL and set the time zone with just a simple command. Using the examples we provided, you’ll be able to manage the time zone setting on your own installation of PostgreSQL.

 

Use the following command in the psql interface to have Postgres return your current time zone

postgres# show TIMEZONE ;

+--------------+
|   TimeZone   |
+--------------+
| Asia/Kolkata |
+--------------+

 

If you’re not sure of the exact name of the time zone you want to set, You can see the timezones from  the following query and continue by selecting the timezone you want.

The pg_timezone_names table also has a is_dst column that indicates if a zone is currently in Daylight Savings Time.

postgres# select * from pg_timezone_names ;
+----------------------------------+--------+------------+--------+
|               name               | abbrev | utc_offset | is_dst |
+----------------------------------+--------+------------+--------+
| NZ-CHAT                          | +1245  | 12:45:00   | f      |
| America/Guadeloupe               | AST    | -04:00:00  | f      |
| America/Fort_Wayne               | EDT    | -04:00:00  | t      |
| America/Panama                   | EST    | -05:00:00  | f      |
| America/Curacao                  | AST    | -04:00:00  | f      |
| America/Godthab                  | -02    | -02:00:00  | t      |
| America/Inuvik                   | MDT    | -06:00:00  | t      |
| America/Boa_Vista                | -04    | -04:00:00  | f      |
| America/Kralendijk               | AST    | -04:00:00  | f      |
| America/Juneau                   | AKDT   | -08:00:00  | t      |
| America/Anchorage                | AKDT   | -08:00:00  | t      |
| America/Asuncion                 | -04    | -04:00:00  | f      |

..

-- Output Trimmed

You can set the timezone in 2 ways.

• session-only variable – this means that it resets to the default value every time you exit and re-enter the psql command-line interface.

• System-level – If you’d like to permanently change the default PostgreSQL time zone for all of your databases, you’ll need to modify the postgresql.conf file. You need to restart the postgresql service after this is done. it's enough to issue SELECT pg_reload_conf();

-- session-only variable
postgres# set TIMEZONE='Singapore';

 
-- system level (permanent)
postgres# alter system set TIMEZONE ='Singapore';

 

You can also set the time zone using an integer value representing the hourly difference from UTC time. You can pass the value as a string or as an integer

SET timezone=-4;

Found this post interesting? Subscribe us  😊😉

Read more 

Configure Streaming Replication in PostgreSQL

All about Physical Replication and Log shipping in Postgres 

Streaming-Replication Sync and Async, benefits of streaming replication over Log-shipping

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.

To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman - Hermione Gingold