Preparing your local Postges DB. Resolving timezone headache for developers.

Preparing your local Postges DB 

Resolving timezone headache for developers.

   To set your PostgreSQL server time to UTC, you can change the setting globally, per database, per user, or for a single session. 

Permanent Global Change

Modify the main configuration file to set the default for the entire server.

1. Locate the file: Find your postgresql.conf file by running this SQL command in your database:

   SHOW config_file;
   

   Edit the file: Open postgresql.conf and find the timezone parameter. Change it to:
2. text

   timezone = 'UTC'
   

   Use code with caution.
3. Apply changes: Reload the configuration without restarting the server by running:

   SELECT pg_reload_conf();
   

   (Alternatively, use pg_ctl reload from your terminal). 

Permanent Database or User Change

If you only want specific databases or users to use UTC, use these SQL commands:

• Per Database:

  ALTERDATABASE your_database_name SET timezone TO'UTC';
  

  Per User:
• ALTER USER your_username SET timezone TO'UTC';

Note: Users will need to reconnect for these changes to take effect. 

Temporary Session Change

To change the time zone for only your current connection, run: 

    SET TIMEZONE 'UTC';

This is useful for testing but will reset once you disconnect. 

Verification

To confirm your current settings, use these commands:

• Check current time zone: SHOW timezone;
• Check current server time: SELECT now();. 

Tip: PostgreSQL internally stores TIMESTAMP WITH TIME ZONE (timestamptz) data in UTC regardless of your server's display settings. Changing the server time zone primarily affects how these timestamps are displayed to you and how "timezone-naive" timestamps are interpreted.