Thread: TimeZone parameter in postgresql.conf

TimeZone parameter in postgresql.conf

From
Majid Azimi
Date:

postgresql documentation says:

All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the timezone configuration parameter before being displayed to the client.

timezone parameter is a session parameter. When all TIMESTAMP WITH TIME ZONEs will be stored as UTC and the client connection will specify the session timezone, then what is the benefit of setting TimeZone parameter in postgresql.conf?

Re: TimeZone parameter in postgresql.conf

From
Steve Crawford
Date:
On 09/05/2012 11:03 AM, Majid Azimi wrote:

postgresql documentation says:

All timezone-aware dates and times are stored internally in UTC. They are converted to local time in the zone specified by the timezone configuration parameter before being displayed to the client.

timezone parameter is a session parameter. When all TIMESTAMP WITH TIME ZONEs will be stored as UTC and the client connection will specify the session timezone, then what is the benefit of setting TimeZone parameter in postgresql.conf?

http://www.postgresql.org/docs/9.1/static/datatype-datetime.html#DATATYPE-TIMEZONES:

...

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

  • If timezone is not specified in postgresql.conf or as a server command-line option, the server attempts to use the value of the TZ environment variable as the default time zone. If TZ is not defined or is not any of the time zone names known to PostgreSQL, the server attempts to determine the operating system's default time zone by checking the behavior of the C library function localtime(). The default time zone is selected as the closest match among PostgreSQL's known time zones. (These rules are also used to choose the default value of log_timezone, if not specified.)

  • 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.

...

In other words is is an explicit default time zone but like many other defaults specified in postgresql.conf you can override it in a session.

Cheers,
Steve

Re: TimeZone parameter in postgresql.conf

From
Jeff Davis
Date:
On Wed, 2012-09-05 at 22:33 +0430, Majid Azimi wrote:
> postgresql documentation says:
>
>         All timezone-aware dates and times are stored internally in
>         UTC. They are converted to local time in the zone specified by
>         the timezone configuration parameter before being displayed to
>         the client.
>
>
> timezone parameter is a session parameter. When all TIMESTAMP WITH
> TIME ZONEs will be stored as UTC and the client connection will
> specify the session timezone, then what is the benefit of setting
> TimeZone parameter in postgresql.conf?

It's essentially just a default so that you don't have to specify it in
the session.

Regards,
    Jeff Davis