PostgreSQL timezone takes Windows OS timezone automatically

Question

While installing Postgresql DB 13.0-1 x64 on Windows, if I comment the "timezone" key on postgresql.conf file then "show timezone" sql query gives me "GMT".

How could I do for the timezone to be the same than my Windows OS's timezone automatically, without running the "ALTER timezone" query manually myself ?

Thanks

For current session you can set timezone manually. See solution in [this post](https://stackoverflow.com/questions/6663765/postgres-default-timezone) — Jesusbrother, May 03 '22 at 14:45
Do you want the time zone from the client machine (which would make sense) or from the server machine (which makes less sense)? — Laurenz Albe, May 03 '22 at 14:58
I want the timezone of the server machine, the one hosting the database. Thanks — clouvis, May 04 '22 at 07:01
@LaurenzAlbe, actually the client and the server machine are the same. In Oracle db the OS timezone is used automatically, there is no need to specify a timezone to the db. I would like the same behaviour — clouvis, May 17 '22 at 07:30

score 0 · Accepted Answer · answered May 17 '22 at 08:01

PostgreSQL does not try to determine the operating system time zone when the server is started, but when the database cluster is created with initdb. During initdb, PostreSQL tries to determine the correct IANA time zone from the operating system. See this excerpt from src/bin/initdb/findtimezone.c:

/*
 * The following block of code attempts to determine which timezone in our
 * timezone database is the best match for the active system timezone.
 *
 * On most systems, we rely on trying to match the observable behavior of
 * the C library's localtime() function. [...]
 *
 * Win32's native knowledge about timezones appears to be too incomplete
 * and too different from the IANA database for the above matching strategy
 * to be of any use. But there is just a limited number of timezones
 * available, so we can rely on a handmade mapping table instead.
 */

[...]

    /*
     * This list was built from the contents of the registry at
     * HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time
     * Zones on Windows 7, Windows 10, and Windows Server 2019.  Some recent
     * additions have been made by comparing to the CLDR project's
     * windowsZones.xml file.
     *
     * The zones have been matched to IANA timezones based on CLDR's mapping
     * for "territory 001".
     */
    {
        /* (UTC+04:30) Kabul */
        "Afghanistan Standard Time", "Afghanistan Daylight Time",
        "Asia/Kabul"
    },
    {
        /* (UTC-09:00) Alaska */
        "Alaskan Standard Time", "Alaskan Daylight Time",
        "America/Anchorage"
    },
    [...]
    {
        /* (UTC-07:00) Yukon */
        "Yukon Standard Time", "Yukon Daylight Time",
        "America/Whitehorse"
    },
    {
        NULL, NULL, NULL
    }

The code then calls localtime(time(NULL)) and gets the system time zone name using strftime() with the format %Z. If that matches an entry in the above list, we are done. Otherwise PostgreSQL scans the HKEY_LOCAL_MACHINE registry entries under SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zone for a match. If none is found, GMT will be used.

That value is stored in postgresql.conf and determines the default time zone. If you remove that entry from postgresql.conf, PostgreSQL will fall back to the default value GMT.

So the solution is not to remove the entry from postgresql.conf, but to run initdb when you need a database cluster on a new machine.

Thanks a lot Laurenz, unfortunately initdb is made when the server is in France , then it is sent and used in India. I will write a script which sets the current timezone to postgresql.conf, then run it in India — clouvis, Jul 05 '22 at 07:03

PostgreSQL timezone takes Windows OS timezone automatically

1 Answers1