DuckDB 时区：支持日历扩展

Author Avatar
Richard Wesley
2022-01-06 · 12 分钟

总结一下：DuckDB ICU 扩展现已提供时区支持。

时区支持是时间分析的常见需求，但规则复杂且有些随意。最受支持的本地化操作库是 Unicode 国际组件 (ICU)。DuckDB 已经通过一个扩展（为避免依赖）提供了使用 ICU 进行排序的字符串比较功能，现在我们已经通过新的 TIMESTAMP WITH TIME ZONE（简称 TIMESTAMPTZ）数据类型，将现有的 ICU 日历和时区函数连接到了主代码中。ICU 扩展预先捆绑在 DuckDB 的 Python 客户端中，并可在其他客户端中选择安装。

在这篇文章中，我们将描述 DuckDB 中时间的工作原理以及新增的时区功能。

什么是时间？

人们认为时间是因果关系的严格发展，但实际上从非线性、非主观的角度来看，它更像是一个由摇摇晃晃、时间扭曲的模糊事物组成的大球。
——神秘博士：眨眼

数据库中的时间可能非常令人困惑，因为我们谈论时间的方式本身就很混乱。本地时间、格林威治标准时间 (GMT)、世界协调时间 (UTC)、时区、闰年、纯公历——这一切看起来都一团糟。但如果退一步看，时间建模实际上相当简单，可以归结为两个部分：时刻和分箱。

时刻

你经常会听到人们（和文档）说数据库时间存储为 UTC。这某种程度上是正确的，但更准确的说法是数据库存储的是时刻。时刻是宇宙时间中的一个点，它们通常表示为从固定时间点（称为纪元）开始的某个时间增量的计数。在 DuckDB 中，固定点是 Unix 纪元 1970-01-01 00:00:00 +00:00，增量是微秒 (µs)。 （请注意，为了避免混淆，本文中我们将使用 ISO-8601 年-月-日表示法来表示时刻。）换句话说，TIMESTAMP 列包含时刻。

SQL 中还有另外三种时间类型：

  • DATE – 从固定日期算起的整日计数。在 DuckDB 中，固定日期是 1970-01-01，同样是 UTC。
  • TIME – 一天内（正数）微秒计数
  • INTERVAL – 用于计算时间差的一组字段。在 DuckDB 中，间隔计算月份、天和微秒。（月份并非完全明确，但当存在时，它们表示 30 天。）

除了 TIME 之外，所有这些其他时间类型都不能带有 WITH TIME ZONE 修饰符（以及更短的 TZ 后缀），但要理解该修饰符的含义，我们首先需要谈谈时间分箱。

时间分箱

时刻相当直接——它们只是一个数字——但分箱是让人感到困惑的部分。如果你处理过连续数据，分箱可能是一个熟悉的想法：你将一组值分成多个范围，并将每个值映射到它所属的范围（或箱）。时间分箱就是对时刻进行这种操作。

Time Zone Instants at the Epoch

时间分箱系统通常被称为日历，但我们现在将避免使用这个术语，因为日历通常与日期相关联，而时间分箱还包括时间的规则。这些时间规则被称为时区，它们也会影响日历所使用的日界线。例如，这是纪元时第二时区的分箱样子：

Two Time Zones at the Epoch

时间分箱最令人困惑的地方在于，分箱时间的方式不止一种，而且不总是明显应该使用哪种分箱。例如，我所说的“今天”是一个时刻的分箱，通常由我居住的地方决定。属于我“今天”的每个时刻都归入那个箱子。但请注意，我将“今天”限定为“我居住的地方”，这种限定决定了正在使用的分箱系统。但“今天”也可以由“事件发生的地方”决定，这将需要应用不同的分箱。

大多数人遇到的最大时间分箱问题发生在夏令时变化时。这个例子包含一个夏令时变化，其中“小时”箱的长度是两个小时！为了区分这两个小时，我们需要包含另一个箱子，其中包含与 UTC 的偏移量。

Two Time Zones at a Daylight Savings Time transition

如本例所示，为了正确地将时刻分箱，我们需要知道适用的分箱规则。它也表明我们不能仅仅使用内置的分箱操作，因为它们不理解夏令时。

朴素时间戳

时刻有时是使用本地分箱系统而不是直接的时刻，从字符串格式创建的。这导致时刻相对于 UTC 发生偏移，从而可能导致夏令时问题。这些被称为朴素时间戳，它们可能构成一个数据清理问题。

清理朴素时间戳需要确定每个时间戳的偏移量，然后更新值以成为一个时刻。对于大多数值，这可以通过与包含正确偏移量的表进行不等于连接来完成，但模糊值可能需要手动修复。也可能通过假设它们是按顺序插入的并使用窗口函数查找“向后跳跃”来纠正模糊值。

一种避免这种情况的简单方法是向非 UTC 字符串添加 UTC 偏移量：2021-07-31 07:20:15 -07:00。DuckDB VARCHAR 转换操作会正确解析这些偏移量，并生成相应的时刻。

时区数据类型

SQL 标准定义了由 WITH TIME ZONE 修饰的时间数据类型。这个术语令人困惑，因为它似乎暗示时区将与值一起存储，但它真正的意思是“使用会话的 TimeZone 设置对这个值进行分箱”。因此，TIMESTAMPTZ 列也存储时刻，但表达了一个“提示”，即它应该使用特定的分箱系统。

有许多操作可以在没有分箱系统的情况下对时刻执行：

  • 比较；
  • 排序；
  • 增量（微秒）差；
  • 与常规 TIMESTAMPs 之间的转换。

这些常用操作已在 DuckDB 主代码库中实现，而分箱操作已委托给 ICU 等扩展。

新的 WITH TIME ZONE 类型与旧类型在显示上的一个微小区别是，新类型将显示为带有 +00 UTC 偏移量。这仅仅是为了在命令行界面中和测试时使类型差异可见。要正确格式化 TIMESTAMPTZ 以便在特定区域设置中显示，需要使用分箱系统。

ICU 时间分箱

DuckDB 已经使用 ICU 扩展来为特定区域设置的字符串排序，因此很自然地将其扩展以暴露 ICU 日历和时区功能。

ICU 时区

支持时区的第一步是添加应该应用的 TimeZone 设置。DuckDB 扩展可以定义和验证自己的设置，ICU 扩展现在就是这样做的：

-- Load the extension
-- This is not needed in Python or R, as the extension is already installed
LOAD icu;

-- Show the current time zone. The default is set to ICU's current time zone.
SELECT * FROM duckdb_settings() WHERE name = 'TimeZone';

TimeZone    Europe/Amsterdam    The current time zone   VARCHAR

-- Choose a time zone.
SET TimeZone = 'America/Los_Angeles';

-- Emulate Postgres' time zone table
SELECT name, abbrev, utc_offset 
FROM pg_timezone_names() 
ORDER BY 1 
LIMIT 5;

ACT ACT 09:30:00
AET AET 10:00:00
AGT AGT -03:00:00
ART ART 02:00:00
AST AST -09:00:00

ICU 时间分箱函数

DuckDB 和 Postgres 等数据库通常提供一些时间分箱函数，例如 YEAR 或 DATE_PART。这些函数是用于常规（纯公历）日历和 UTC 时区的单一分箱系统的一部分。请注意，转换为字符串是一种分箱操作，因为生成的文本包含分箱值。

由于需要自定义分箱的时间戳具有不同的数据类型，ICU 扩展可以定义与 TIMESTAMPTZ 绑定的附加函数：

  • + – 将 INTERVAL 添加到时间戳
  • - – 从时间戳中减去 INTERVAL
  • AGE – 计算描述两个时间戳（或一个时间戳与当前时刻）之间月/日/微秒差的 INTERVAL。
  • DATE_DIFF – 计算两个时间戳之间的部分边界跨越次数
  • DATE_PART – 提取指定的时间戳部分。这包括部分别名函数，例如 YEAR。
  • DATE_SUB – 计算两个时间戳之间完整部分的数量
  • DATE_TRUNC – 将时间戳截断到给定精度
  • LAST_DAY – 返回月份的最后一天
  • MAKE_TIMESTAMPTZ – 从各部分构造一个 TIMESTAMPTZ，包括可选的最终时区说明符。

我们尚未为 TIMETZ 实现这些函数，因为此类型实用性有限，但将来添加并不困难。我们也没有实现字符串格式化/转换为 VARCHAR，因为类型转换系统尚不可扩展，并且我们正在使用的当前 ICU 构建未嵌入此数据。

ICU 日历支持

ICU 还可以对一些非公历日历执行分箱操作。我们通过 Calendar 设置和 icu_calendar_names 表函数添加了对这些日历的支持：

LOAD icu;

-- Show the current calendar. The default is set to ICU's current locale.
SELECT * FROM duckdb_settings() WHERE name = 'Calendar';

Calendar    gregorian   The current calendar    VARCHAR

-- List the available calendars
SELECT DISTINCT name FROM icu_calendar_names()
ORDER BY 1 DESC LIMIT 5;

roc
persian
japanese
iso8601
islamic-umalqura

-- Choose a calendar
SET Calendar = 'japanese';

-- Extract the current Japanese era number using Tokyo time
SET TimeZone = 'Asia/Tokyo';

SELECT
     era('2019-05-01 00:00:00+10'::TIMESTAMPTZ),
     era('2019-05-01 00:00:00+09'::TIMESTAMPTZ);

235  236

注意事项

ICU 在行为和表示上与 DuckDB 的实现存在一些差异。这些问题希望是微不足道的，应该只对认真的时间迷才值得关注。

  • ICU 使用 DOUBLE 表示毫秒计数来表示时刻。这导致其在远离纪元（例如，在第一个千年左右）时会失去精度。
  • ICU 对 1582-10-15 之前使用儒略历而不是纯公历。这意味着切换日期之前的日期会有所不同，尽管 ICU 会提供当时实际书写的日期。
  • ICU 通过使用部分增量而不是像 DuckDB 和 Postgres 那样使用前一个月的长度来计算年龄。

未来工作

时间分析是一个广阔的领域，虽然 ICU 时区支持是向前迈出的一大步，但仍有许多工作可以做。其中一些项目是核心 DuckDB 改进，可以使所有时间分箱系统受益，有些则暴露了更多 ICU 功能。此外，还有通过扩展编写其他自定义分箱系统的前景。

DuckDB 功能

以下是一些所有分箱系统都可以受益的通用项目：

  • 添加一个 DATE_ROLL 函数，模拟 ICU 日历的 roll 操作，用于在包含的箱子周围“旋转”；
  • 使转换操作可扩展，以便扩展可以添加自己的支持；

ICU 功能

ICU 是一个非常丰富的库，历史悠久，现有库还有很多可以做的事情：

  • 创建一个更通用的 MAKE_TIMESTAPTZ 变体，它接受一个包含各部分的 STRUCT。这对于某些非公历日历可能很有用。
  • 扩展嵌入数据以包含区域时间信息（例如月份名称）并支持本地日期的格式化（to_char）和解析（to_timestamp）。这里的一个问题是 ICU 日期格式语言比 Postgres 语言更复杂，因此可能需要多个函数（例如 icu_to_char）；
  • 扩展分箱函数以接受每行的日历和时区规范，以支持行级时间分析，例如“这发生在一天中的什么时间”？

关注点分离

由于时区数据类型在主代码库中定义，而日历操作由扩展提供，因此现在可以编写具有自定义日历和时区支持的应用程序特定扩展，例如：

  • 财务 4-4-5 日历；
  • ISO 周基年份；
  • 表驱动日历；
  • 带闰秒的天文日历；
  • 有趣的日历，例如夏尔纪年法和法兰西共和历！

结论与反馈

在这篇博客文章中，我们描述了通过 ICU 扩展实现的新 DuckDB 时区功能。我们希望所提供的功能能够支持涉及时区的时间分析应用程序。我们也期待看到我们的用户构想出任何自定义日历扩展！

最后但同样重要的一点是，如果您在使用我们的集成时遇到任何问题，请在 DuckDB 的问题追踪器中提出问题！

本文中