TimeZone configuration is set per connection, not the system as documented

[era127]

The TimeZone configuration setting is documented to be for the database system but I have replicated an example in both the rust and Julia api where the TimeZone configuration setting is per connection, not the database. In the example below I update the configuration setting in on connection to demonstrate the change, but a new connection does not use that setting.

If the TimeZone configuration setting is expected to be set per connection and not the database, then maybe we could add something to the documentation to explain that detail. Thanks.

using DuckDB
db = DuckDB.DB()
DuckDB.execute(db, "LOAD icu;")
con = DuckDB.connect(db)

# demonstrate the change in behavior of make_timestamptz when the timezone is set
println(DuckDB.execute(con, "SELECT make_timestamptz(2000,01,20,03,30,59)")) # "2000-01-20T08:30:59"
println(DuckDB.execute(con, "SET TimeZone = 'UTC'"))
println(DuckDB.execute(con, "SELECT make_timestamptz(2000,01,20,03,30,59)")) # "2000-01-20T03:30:59"

# The second connection does not use the timezone that was previously set for the system.
con2 = DuckDB.connect(db)
println(DuckDB.execute(con2, "SELECT make_timestamptz(2000,01,20,03,30,59)")) # "2000-01-20T08:30:59"

[Tishj]

Actually this is perhaps a piece of behavior that is not properly documented elsewhere:

    if (scope == SetScope::GLOBAL) {
        config.SetOption(name, std::move(target_value));
    } else {
        auto &client_config = ClientConfig::GetConfig(context);
        client_config.set_variables[name] = std::move(target_value);
    }

extension settings can be either set on the DBConfig or the ClientContext By default SET uses scope AUTOMATIC, not GLOBAL

Probably here?

[Emenemer]

Facing this same issue. I am currently using duckdb (0.10.1) through python in combination with sqlachemy (2.0.28). When retrieving timestamp data we're facing an auto-conversion of the timezone to our local timezone.

Setting the timezone explicitly in a global scope: SET GLOBAL TimeZone TO 'UTC' does not seem to persist and is overwritten every time a new session is opened, setting it back to our local timezone. Our current workaround is to set the timezone everytime a session is opened, but this is far from ideal.

Any ideas why the global set is not persistent?

[Mause]

Our current workaround is to set the timezone everytime a session is opened, but this is far from ideal.

This behaviour is expected, barring the global config file that only the cli uses, there is no functionality for persisting configuration between sessions. Global here refers to all connections to a given running database

[Emenemer]

It seems highly unwanted behaviour to not be able to set the global (cross session) settings of the db. For timeseries data this would mean that you always need to set the timezone to UTC (or whichever preferred TZ) from your local timezone between sessions of working with the same duckdb file. Otherwise the queried data will always have the incorrect timezone attributed to it. And now I am only zooming in to the TimeZone setting, probably there are similar issues for more of the available settings.

Is this really the intended behaviour? Or is there another way of specifying the global settings of which I am unaware?

[era127]

The default scope for TimeZone is documented as being Global, which I assume in the R api would be for the Database type, while Local or Session would be for a Connection type. Does the Global scope refer to a database and does Session or Local refers to a Connection? It is confusing because the documentation for SET lists configurations with a Local default but it says Local is not yet implemented.