Is it possible to set timezone for a schema - Postgresql

Question

Is it possible to set the timezone for one schema? Do not want to set Postgresql(v.12) server level.

Please suggest if any ways to this.

• tombh: I'd just like to add some context as to why this is an important question. A schema is generally assumed to be a canonical, serialised representation of the database. However a schema loses some of that reliability if its timestamp column values aren't guaranteed to match the timezone of the server. Hence why being able to specify the data's timezone in a schema is a legitimate question.

Answer 1

No.

You can either set the timezone at database level, or at session level (... or directly when accessing a date-like datatype, using the at time zone construct).

From the documentation:

The TimeZone configuration parameter can be set in the file postgresql.conf, or in any of the other standard ways described in Chapter 19. There are also some special ways to set it:

• The SQL command SET TIME ZONE sets the time zone for the session. This is an alternative spelling of SET TIMEZONE TO with a more SQL-spec-compatible syntax.

• The PGTZ environment variable is used by libpq clients to send a SET TIME ZONE command to the server upon connection.

• Laurenz Albe: You should add that it can be set on the user level. Perhaps that solves the OP's unknown problem.
• Venkat: Please share the commands or settings to set at database level.
• GMB: @Venkat: the documentation that I linked in my answer explains how to do that.
• Venkat: Thanks. The ALTER DATABASE command allows global settings to be overridden on a per-database basis. [ ALTER SYSTEM command provides a SQL-accessible means of changing global defaults; it is functionally equivalent to editing postgresql.conf. The ALTER ROLE command allows both global and per-database settings to be overridden with user-specific values.]

Answer 2

I was able to change the timezone of a schema (permanently) with:

set timezone to 'UTC';

Reconnecting and/or restarting my postgresql service shows the setting remained. Confirm with: show timezone; or select now();

Switch to schema2 with: \c schema2

I'm using Postgres 12 on Ubuntu.