Using the global `TimeZone` config in the `Properties` object passed to `DriverManager.getConnection` fails with error: "The following options were not recognized: TimeZone"

[jivanic-demystdata]

This error seems counter-intuitive as this config is documented as global: https://duckdb.org/docs/stable/configuration/overview.html#global-configuration-options

Shouldn't this Properties object allow us to configure the global timezone of our statements?

Reproducer (scala code):

val config = new Properties()
config.put("TimeZone", "UTC")
DriverManager.getConnection(url, config).unwrap(classOf[DuckDBConnection])

Error:

        java.sql.SQLException: Invalid Input Error: The following options were not recognized: TimeZone
        	at org.duckdb.DuckDBNative.duckdb_jdbc_startup(Native Method)
        	at org.duckdb.DuckDBConnection.newConnection(DuckDBConnection.java:62)
        	at org.duckdb.DuckDBDriver.connect(DuckDBDriver.java:114)
        	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:683)
        	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:191)
        	...

cc @guizmaii

[staticlibs]

Hi, thanks for the report! The error message is confusing, TimeZone config option definitely exists. Both following variants work for me:

Properties config = new Properties();
config.put("TimeZone", "UTC");
try (Connection conn = DriverManager.getConnection("jdbc:duckdb:", config);
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery("SELECT current_setting('TimeZone')")) {
    rs.next();
    System.out.println(rs.getString(1));
}

try (Connection conn = DriverManager.getConnection("jdbc:duckdb:;TimeZone=UTC;");
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery("SELECT current_setting('TimeZone')")) {
    rs.next();
    System.out.println(rs.getString(1));
}

Is is possible that in your case the option name is passed with some additional symbols (spaces or zeros) at the end, like "TimeZone "?

[elefeint]

I've seen this error when ICU extension could not be autoloaded (maybe the home directory lacks write access).

[jivanic-demystdata]

@staticlibs I tried both config.put("TimeZone", "UTC"); and DriverManager.getConnection("jdbc:duckdb:;TimeZone=UTC;") and both generates the same error.

I'm using duckdb-java v1.4.1.0

@elefeint This happens when I run my tests. I'm not sure why I'd have any write access restrictions 🤔

[jivanic-demystdata]

Ok I did some more tests and found some interesting stuff.
I didn't show you all the configs I use.

Here's how I setup my Connection:

val needsS3: Boolean = ... // Not important here

val config = new Properties()
config.put("TimeZone", "UTC")
config.put("enable_external_access", needsS3)
config.put("allow_community_extensions", false)
config.put("allow_unsigned_extensions", false)
config.put("autoinstall_known_extensions", false)
config.put("autoload_known_extensions", false)
config.put("allow_persistent_secrets", false)
config.put("allow_unredacted_secrets", false)
config.put("allowed_directories", "[ ... ]")
DriverManager.getConnection(url, config).unwrap(classOf[DuckDBConnection])

With config.put("autoload_known_extensions", false) the error message is the one I initially reported in this ticket:

      - DuckDbClientLive - ::executeS3QueryRaw - cancellation - with timeout - query is cancelled/interrupted
        java.sql.SQLException: Invalid Input Error: The following options were not recognized: TimeZone
        	at org.duckdb.DuckDBNative.duckdb_jdbc_startup(Native Method)
        	at org.duckdb.DuckDBConnection.newConnection(DuckDBConnection.java:62)
        	at org.duckdb.DuckDBDriver.connect(DuckDBDriver.java:114)
        	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:683)
        	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:191)
                 ...

If I change this config value from false to true: config.put("autoload_known_extensions", true), then I still have an error, but a different one:

        java.sql.SQLException: Invalid Input Error: To set the TimeZone setting, the icu extension needs to be loaded. But it could not be autoloaded.
        	at org.duckdb.DuckDBNative.duckdb_jdbc_startup(Native Method)
        	at org.duckdb.DuckDBConnection.newConnection(DuckDBConnection.java:62)
        	at org.duckdb.DuckDBDriver.connect(DuckDBDriver.java:114)
        	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:683)
        	at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:191)
                 ...

I'm not sure to understand why I have this error.
Shouldn't icu be aumatically loaded in this case? I allow it and it is needed to set the TimeZone as the error explains it 🤔

[jivanic-demystdata]

Ok, here's how I bypass the issue for now:

val config = new Properties()
config.put("enable_external_access", needsS3)
config.put("allow_community_extensions", false)
config.put("allow_unsigned_extensions", false)
config.put("autoinstall_known_extensions", false)
config.put("autoload_known_extensions", false)
config.put("allow_persistent_secrets", false)
config.put("allow_unredacted_secrets", false)
config.put("allowed_directories", s"[ ${java.lang.System.getProperty("java.io.tmpdir")} ]" )
// Needed to install plugins
config.put("extension_directory", s"${java.lang.System.getProperty("java.io.tmpdir")}/duckdb_extensions")
val conn = DriverManager.getConnection(url, config).unwrap(classOf[DuckDBConnection])
...
// later, when I create a new Statement:
val stmt = conn.createStatement()
stmt.execute("INSTALL icu; LOAD icu; SET TimeZone = 'UTC';") // configure the timezone. Needs to load `icu` first

// later, I can use the configured Statement
...