Wrong Default value of TimeZone in the duckdb settings

[ujaval403]

What happens?

The TimeZone value in settings is in UTC+XXXX or UTC-XXXX which is not supported value in the timezone
so we are not getting expected values in query results.

https://duckdb.org/docs/sql/data_types/timezones.html

To Reproduce

Screen.Recording.2024-10-15.at.7.05.48.AM.mp4

install icu;
load icu;

CREATE or REPLACE TABLE events (
   id INTEGER,
   event_time TIMESTAMPTZ
);

insert into events values (1, epoch_ms(1680308400000)); -- 1 April 2023 12:20:00 AM UTC

select *, date_trunc('month', event_time) from events;

to check the current value

select * from duckdb_settings() where name='TimeZone';

Right Result after

set TimeZone = 'America/Los_Angeles';

select *, date_trunc('month', event_time) from events;

Browser/Environment:

129.0.6668.100

Device:

Mac OS

DuckDB-Wasm Version:

1.28.1-dev258.0

DuckDB-Wasm Deployment:

Custom

Full Name:

Ujaval Bhatt

Affiliation:

DevRev

[carlopi]

I believe this is fixed bumping to duckdb v1.4.0

[jraymakers]

This behavior does not appear to be different in 1.4.0:

DuckDB Web Shell
Database: v1.4.0
Package:  @duckdb/duckdb-wasm@1.30.1-dev4.0

Connected to a local transient in-memory database.
Enter .help for usage hints.

duckdb> select current_setting('timezone');
┌─────────────────────────────┐
│ current_setting('timezone') │
╞═════════════════════════════╡
│ UTC-0800                    │
└─────────────────────────────┘

@carlopi What would you expect to be different in 1.4.0?

[jraymakers]

Might this change address this issue?

@hawkfish In case you have any insight.

[hawkfish]

Might this change address this issue?

Unfortunately no, that was a narrow fix for a specific case. The situation here is that the environment is using a TZ that ICU does not understand.

In general, it is a Good Idea to set the TZ explicitly rather than relying on the environment. In this case, UTC-0800 does not have DST, which may be causing some of the calculation issues. Moreover, I believe ICU will default to UTC+0000 if it doesn't understand the environment's, which may also be the source of the discrepancy.

[hawkfish]

The names of the non-DST offset TZs in ICU are Etc/GMT+H so the equivalent to what is in your environment is Etc/GMT-8. But we are not translating those formats.

[jraymakers]

I see, thanks for clarifying.

I agree that setting the TZ explicitly may be called for here. It would be nicer, of course, if ICU could gather a TZ from the environment that it understands when in the Wasm environment.

[hawkfish]

Some of our ICU issues might be resolved by moving to a more modern version (we are still on 66.0) but the TZ data is shared by all versions, so this is just not a name that ICU supports. It does seem to have a Wikipedia entry, so we could probably just add them at ICU startup (there's only 27 offsets).

[jraymakers]

I think forms like "UTC-0800" are technically considered "UTC offsets", not, strictly speaking, "time zones", because they don't correspond to a geographical area. That may explain why they're not in ICU's list. It certainly would be convenient to be able to use them as "time zones", though.

[hawkfish]

ICU does have "offset" time zones (Etc/GMT±N). I'd be worried about people not realising that the DST rules were not being applied, which is why I think they should be explicit. Its easy enough to translate the offsets to the ICU TZs, but I'm not sure hiding this issue is in the user's best interests?

[hawkfish]

OK I did a bit more digging, and ICU is very dependent on TZ names being Olson IDs. I think the best one can hope for here is to catch it at initialization time and translate it.

I can put this in but I have been unable to find a way to test it, so I may need you to try it.

[jraymakers]

If you can get it into a release of duckdb-wasm, I can test it.

If you want to test before that, I can probably test it from a duckdb branch, by building duckdb-wasm against that branch.