How to Set Timezone for Postgres Psql

postgres default timezone

The time zone is a session parameter. So, you can change the timezone for the current session.

See the doc.

set timezone TO 'GMT';

Or, more closely following the SQL standard, use the SET TIME ZONE command. Notice two words for "TIME ZONE" where the code above uses a single word "timezone".

SET TIME ZONE 'UTC';

The doc explains the difference:

SET TIME ZONE extends syntax defined in the SQL standard. The standard allows only numeric time zone offsets while PostgreSQL allows more flexible time-zone specifications. All other SET features are PostgreSQL extensions.

How to set timezone for Postgres psql?

The psql doc says:

-v assignment
--set=assignment
--variable=assignment
Perform a variable assignment, like the \set internal command. Note that 
you must separate name and value, if any, by an equal sign on the command line....

But with the timezone it does not seem to work, perhaps because because of this:

 These assignments are done during a very early stage of start-up, 
 so variables reserved for internal purposes might get overwritten later.

So, it seems you must either use the SET command inside psql, or either set the PGTZ environment variable:

PGTZ=PST8PDT psql -c 'show timezone'

Of course, if you are OK with setting the timezone globally for the user (not just for this individual psql instance), you might set that variable in its .bashrc file (if in Linux)

How do I change the timezone to UTC in postgres permanently?

There are many ways to set configuration variables. But most of these setting can be overruled by the client inside its transaction. (Some settings require a server restart or have other restrictions, but not timezone.)

To make absolutely sure, some operations happen with timezone = 'UTC', you could encapsulate that in a server-side function with its own setting, overriding any user-setting. Find a code example in this related answer:

How does the search_path influence identifier resolution and the "current schema"

But while that fits the wording of your question, I don't think that's what you are looking for. You probably just need to reload after changing postgresql.conf to set the default for new sessions.

postgresql set timezone for materialized view

If you code your materialized view to depend on the current setting of database parameters, you are in trouble, because many of them can be changed on the session level. Don't do that.

In the current case, avoid anything that casts date or timestamp without timezone to timestamp with time zone or vice versa, because timezone will clandestinely enter into any such conversions.

Always specify the time zone explicitly:

to convert from date or timestamp to timestamp with time zone, use
```
current_date AT TIME ZONE 'whatever'
```
to convert from timestamp with time zone to date or timestamp, use
```
current_timestamp AT TIME ZONE 'whatever'
```

Both operators are called AT TIME ZONE, but they are different.

I see that your problem is that there is a large body of pre-existing view definitions that do not adhere to this principle. Perhaps you can get away with something like:

WITH setzone AS (
   SELECT set_config('timezone', 'UTC', TRUE)
)
SELECT /* your query */;

But you need to change the query so that it references setzone somewhere in the query (you could CROSS JOIN it), so that it is executed.

How to set timezone inside a postgres function

You can do it in two different ways:

You can use the EXECUTE command, with a fully-constructed string (i.e.: without USING¹), and use the SET [LOCAL] TIME ZONE statement, as you were doing in your function.

This function will let you test it:

CREATE OR REPLACE FUNCTION test_set_time_zone(_new_time_zone TEXT)
   RETURNS TEXT
   SECURITY DEFINER
   LANGUAGE plpgsql
AS $$
BEGIN
    EXECUTE 'SET LOCAL TIME ZONE ''' || _new_time_zone || ''';' ;
    RETURN current_setting('timezone')  ;
END;
$$;

You can check it with:

SELECT test_set_time_zone('Europe/Paris');


| test_set_time_zone |
| :----------------- |
| Europe/Paris       |

You can use the set_config() function in a similar fashion, being called via PERFORM, setting 'timezone' as the parameter to configure, and deciding whether to make the setting local or global.

You can check it with:

CREATE OR REPLACE FUNCTION test_set_time_zone_2 (_new_time_zone TEXT)
   RETURNS TEXT
   SECURITY DEFINER
   LANGUAGE plpgsql
AS $$
BEGIN
    PERFORM set_config('timezone', _new_time_zone, true /* local */) ;
    RETURN current_setting('timezone')  ;
END;
$$;

SELECT test_set_time_zone_2('US/Central') ;


| test_set_time_zone_2 |
| :------------------- |
| US/Central           |

You can check both functions at dbfiddle here I didn't try any global setting, because my guess is that in this platform a web user won't have the proper privileges; my best guess is that you can do the changes global in you system doing equivalent things.

¹⁾ _{As per comment from Pavel Stehule: USING clause is available for execution plan parameters only. SET has not execution plan - and then the USING clause is not available.}