ICU extension

Active contributors: Richard Wesley, Mytherin, Mark

Purpose

extension/icu/ adds time zones, locale-aware collations, calendar arithmetic, and locale-sensitive STRPTIME / STRFTIME to DuckDB. It bundles a curated subset of ICU sources in extension/icu/third_party/. Without this extension, DuckDB has no notion of time zones beyond UTC and uses byte-wise string collations.

Directory layout

extension/icu/
├── icu_extension.cpp        Registration entry
├── icu-timezone.cpp         AT TIME ZONE, time-zone resolution, list of zones
├── icu-current.cpp          NOW(), CURRENT_TIMESTAMP with TZ
├── icu-dateadd.cpp          DATE_ADD across DST + calendars
├── icu-datesub.cpp          DATE_SUB / age
├── icu-datepart.cpp         EXTRACT, DATE_PART
├── icu-datetrunc.cpp        DATE_TRUNC
├── icu-datefunc.cpp         Shared helpers
├── icu-makedate.cpp         MAKE_DATE / MAKE_TIMESTAMP
├── icu-strptime.cpp         Locale-aware STRPTIME / STRFTIME
├── icu-list-range.cpp       range() returning intervals
├── icu-table-range.cpp      generate_series with intervals
├── icu-timebucket.cpp       time_bucket / time_bucket_gapfill
├── README.md                Per-area notes (long; see for nuance)
├── third_party/             Vendored ICU subset
├── scripts/                 Generators that pull updated ICU snapshots
└── filters.json             ICU build filter

What it provides

Function	Purpose
AT TIME ZONE 'Europe/Berlin'	Convert between local and UTC timestamps.
time_bucket(interval, ts, [tz])	Group by aligned time buckets, DST-aware.
time_bucket_gapfill(...)	Time-bucketed gap-filling (used in TimescaleDB-style queries).
strptime(str, fmt, locale)	Locale-aware string parsing.
strftime(ts, fmt, locale)	Locale-aware formatting.
date_trunc('quarter', ts) etc.	Truncate honoring calendars.
date_add, date_sub, date_diff, date_part	DST-correct calendar arithmetic.
make_timestamp_tz(...)	Construct zoned timestamps.
list_range(...) and range(...) table function	Generate timestamps with INTERVAL step.

PRAGMA timezone and SET TimeZone = 'America/New_York' route through this extension. The available zone list comes from ICU.

Collations

icu_extension.cpp registers locale collations (e.g., de, de_phonebook, nocase) so that SELECT a COLLATE de FROM ... performs locale-aware ordering. See the README at extension/icu/README.md for the full list of registered locales and collation options.

How it works

ICU's Calendar, TimeZone, BreakIterator, and Collator classes drive the implementations. The DuckDB wrappers in icu-*.cpp keep ICU calls behind a small adapter (ICUDateFunc::BindData and helpers) so that the rest of the engine never sees ICU types directly. That isolation is what allows the engine to be built without the ICU extension when needed.

The vendored ICU subset is intentionally narrow — only the date/time/calendar/collation pieces. The build system uses icu_config.py and filters.json to keep the binary size manageable.

Integration points

• Type system: Adds the TIMESTAMP_TZ type's behavior — without ICU, TIMESTAMP_TZ is treated as UTC-only.
• Functions: Replaces (when loaded) several built-in date functions with ICU-aware versions registered by overriding catalog entries.
• Settings: TimeZone, Calendar, and IntervalStyle are exposed via PRAGMA and routed through ICU.
• Build: A separate CMake target; the Makefile target BUILD_ICU=1 make (set automatically when BUILD_ALL_EXT=1).

Entry points for modification

• Updating ICU: see extension/icu/scripts/ for the snapshot pull and filters.json to broaden the data set.
• Adding a new time-zone-aware function: follow the pattern in icu-dateadd.cpp (define a function table, plug into the catalog through icu_extension.cpp).
• Tests: test/sql/timezone/, test/sql/function/timestamp_tz/, test/sql/collate/.

Key source files

File	Purpose
extension/icu/icu_extension.cpp	Registers all ICU-backed functions and collations.
extension/icu/icu-timezone.cpp	Time-zone arithmetic.
extension/icu/icu-timebucket.cpp	Bucketing functions.
extension/icu/icu-strptime.cpp	Locale-aware parsing/formatting.
extension/icu/README.md	Per-area notes.

See extensions/index for the broader extension picture and systems/function for how these functions plug into overload resolution.