Summary of the problem

CockroachDB uses the Go language's idea of where time zones are defined, and this is non-standard. This breaks time zone support in some environments, most notably Windows.

Workarounds

• run on an OS platform where Go has proper timezone support (e.g. Linux)
• get a copy of zoneinfo.zip from the Go sources and set the env var ZONEINFO to point to it
• install the Go sources and set GOROOT to point to them

Note: since 20.1 a node will refuse to start up (#45680) if the tzdata is not available. It is possible to forcefully start a node (and cause inconsistent time results in SQL) via the env var COCKROACH_INCONSISTENT_TIME_ZONES.

Description of the problem

CockroachDB currently uses Go's standard time.LoadLocation to load time zone data.

However Go's standard time package is very simple/dumb. It takes the time zone name given as a file name, tries to open that in the system-wide tzdata directory, and satisfies itself with what it finds there. If the file does not exist (as would happen if the given string is mis-cased), tough luck.

There are multiple problems with this design:

• it doesn't recognize time zone names if the casing of the provided string does not match the casing of the file name. sql: perform time zone name comparison case-insensitively #36847
• on some platforms (eg Windows) the file names are not the same as on POSIX. sql: time zone name not recognized when running CockroachDB on windows #32415
• different system underlying different nodes in the same CockroachDB cluster can have different versions / names for the time zone files. sql: tzinfo can be inconsistent across nodes #31978
• in general, one should never take a user-provided string and use that to open a file -- for example on Windows, the current Go library code would enable the user of an app like CockroachDB to deadlock the process, by requesting e.g. the time zone con or com1 or one of the special windows file names.

Solution

CockroachDB should:

• embed its own copy of a time zone database (like postgres does? I'm not sure)
• it should only accept time zone names that are known to be valid/exist when cockroachdb was built (to avoid trying to open a file with a special name)
• it should provide its own custom time zone loader, which knows how to find time zone names case-insensitively.

Another approach would be to scan the system-wide timezone database upon process start-up and then normalize the name cases in an in-RAM cache.