api: check whether we should get the connection time zone for duckdb/pyspark

[MarcoGorelli]

Currently, for pyspark, we map TimestampType to Datetime(time_zone='UTC'). Is this right? In DuckDB, you can get the time zone of the connection. Is there anything similar for PySpark? Should we be using that?

[MarcoGorelli]

We really need to make a decision on this one

Currently, for duckdb, it's possible to get the time zone with:

duckdb.sql("""select current_setting('timezone')""").fetchone()[0]

This is a bit more expensive than I'd like it to be:

In [14]: %timeit duckdb.sql("""select current_setting('timezone')""").fetchone()[0]
749 μs ± 27.6 μs per loop (mean ± std. dev. of 7 runs, 1,000 loops each)

---

Proposed solution

When computing .schema, we don't compute the time zone. We just either store None or Some(Deferred). We only evaluate the time zone if the user:

• asks for it, e.g. they execute something like df.collect_schema()['a'].time_zone
• prints the schema. In that case, it would be good to show it, and it's a bit surprising to me that DuckDB / PySpark don't show it by default

Note that we can't cache this value, as it's possible for users to change the time zone

In [16]: duckdb.sql("""set timezone = 'Europe/Amsterdam'""")

In [17]: duckdb.sql("""select current_setting('timezone')""").fetchone()[0]
Out[17]: 'Europe/Amsterdam'

[MarcoGorelli]

This would be a better way to check duckdb/duckdb#17578 (comment)

rel1.query('duckdb_settings()', 'select value from duckdb_settings() where name = \'TimeZone\'').fetchone()[0]

as then the sql query would just be

In [13]: rel2.query('duckdb_settings()', 'select value from duckdb_settings() where name = \'TimeZone\'').sql_query()
Out[13]: 'SELECT "value" FROM duckdb_settings() WHERE ("name" = \'TimeZone\')'

without it depending in any way on the original relation

It's still a bit annoying that, given that users can modify the configuration's time zone, we can't cache this

[MarcoGorelli]

To make this even more uncomfortable, we currently cache the schema, but if the users are allowed to change it mid-query (as duckdb allows), then our cached schema is no longer accurate

uurrgghh

Part of me thinks we should just store this as Datetime(time_zone='<unknown>') for backends where the time zone isn't part of the data type. In fact, I'm more and more leaning towards that...

[MarcoGorelli]

To make this even more uncomfortable, we currently cache the schema, but if the users are allowed to change it mid-query (as duckdb allows), then our cached schema is no longer accurate

What we could do is only cache the native schema

[MarcoGorelli]

SQLFrame issue: eakmanrq/sqlframe#406

PySpark solution: print(df.sparkSession.conf.get('spark.sql.session.timeZone'))

Looks like this one's pretty fast already, so perhaps it's less pressing to cache?

In [2]: %timeit df.sparkSession.conf.get('spark.sql.session.timeZone')
230 μs ± 12.8 μs per loop (mean ± std. dev. of 7 runs, 1,000 loops each)