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

Answer 1

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.