Materialize项目中的时间戳类型详解：timestamp与timestamptz

Materialize项目中的时间戳类型详解：timestamp与timestamptz

materialize The data warehouse for operational workloads. 项目地址: https://gitcode.com/gh_mirrors/mat/materialize

概述

在Materialize项目中，时间戳类型是处理日期和时间数据的重要组成部分。Materialize提供了两种时间戳类型：timestamp和timestamp with time zone（简写为timestamptz）。这两种类型都以UTC格式存储数据，但在输入输出处理上有所不同。

基本特性对比

timestamp类型

| 特性 | 说明 | |------|------| | 快速语法 | TIMESTAMP '2007-02-01 15:04:05' | | 存储大小 | 8字节 | | 最小值 | 公元前4713年 | | 最大值 | 公元294276年 | | 最高精度 | 1微秒 |

timestamp with time zone类型

| 特性 | 说明 | |------|------| | 快速语法 | TIMESTAMPTZ '2007-02-01 15:04:05+06' | | 别名 | timestamptz | | 存储大小 | 8字节 | | 最小值 | 公元前4713年 | | 最大值 | 公元294276年 | | 最高精度 | 1微秒 |

语法详解

时间戳类型的完整语法结构如下：

TIMESTAMP [WITH TIME ZONE | WITHOUT TIME ZONE] [ (precision) ] 'date_str time_str [tz_offset]'

• WITH TIME ZONE：应用时区偏移量
• WITHOUT TIME ZONE：忽略时区偏移量（默认选项）
• TIMESTAMPTZ：TIMESTAMP WITH TIME ZONE的简写形式
• precision：指定小数秒的精度位数（默认为6位，即微秒级）
• date_str：日期字符串，支持Y-M-D、Y M-D、Y M D或YMD格式
• time_str：时间字符串，格式为H:M:S.NS
• tz_offset：时区偏移量（相对于UTC的小时数）

核心特点

1. UTC存储：两种类型都以UTC格式存储数据，确保时间数据的一致性。

2. 时区处理差异：

   • timestamp类型完全忽略时区信息
   • timestamptz类型在输入时会考虑时区偏移量，将其转换为UTC存储，输出时再转换回UTC时间

3. 客户端假设：Materialize假设所有客户端都期望UTC时间，目前不支持其他时区设置。

类型转换

可转换类型

timestamp和timestamptz可以相互隐式转换，也可以转换为以下类型：

• 转date类型（通过赋值）
• 转text类型（通过赋值）
• 转time类型（通过赋值）

可被转换类型

以下类型可以转换为timestamp或timestamptz：

• date类型（隐式转换）
• text类型（显式转换）

运算支持

时间戳类型支持以下运算：

| 运算 | 结果类型 | |------|----------| | date + interval | timestamp/tz | | date - interval | timestamp/tz | | date + time | timestamp/tz | | timestamp/tz + interval | timestamp/tz | | timestamp/tz - interval | timestamp/tz | | timestamp/tz - timestamp/tz | interval |

实际示例

基本timestamp示例

SELECT TIMESTAMP '2007-02-01 15:04:05' AS ts_v;

输出结果：

        ts_v
---------------------
 2007-02-01 15:04:05

带时区的timestamp示例

SELECT TIMESTAMPTZ '2007-02-01 15:04:05+06' AS tstz_v;

输出结果：

         tstz_v
-------------------------
 2007-02-01 09:04:05 UTC

注意：输入时间15:04:05+06（东六区时间）被转换为UTC时间09:04:05。

最佳实践建议

1. 在需要处理跨时区应用时，优先使用timestamptz类型，它能正确处理时区转换。

2. 对于不需要时区信息的纯本地时间记录，使用timestamp类型更为简洁。

3. 注意精度设置，默认微秒级精度能满足大多数场景，特殊需求可调整precision参数。

4. 进行时间计算时，确保操作数类型匹配，必要时使用显式类型转换。

通过合理使用Materialize提供的时间戳类型，可以确保时间数据在分布式系统中的一致性和准确性，为实时数据处理提供可靠的时间基准。

materialize The data warehouse for operational workloads. 项目地址: https://gitcode.com/gh_mirrors/mat/materialize