日期与时间

概述

名称	别名	存储大小	精度	最小值	最大值	格式
DATE		4 bytes	天	0001-01-01	9999-12-31	`YYYY-MM-DD`
TIMESTAMP	DATETIME	8 bytes	微秒	0001-01-01 00:00:00.000000	9999-12-31 23:59:59.999999 UTC	`YYYY-MM-DD hh:mm:ss[.fraction]`，显示时使用会话时区
TIMESTAMP_TZ	TIMESTAMP WITH TIME ZONE	8 bytes	微秒	0001-01-01 00:00:00.000000	9999-12-31 23:59:59.999999 UTC	`YYYY-MM-DD hh:mm:ss[.fraction]±hh:mm`，存储 UTC 值和偏移

DATE 仅保留日历日期值，TIMESTAMP 在内部以 UTC 存储，但会通过当前会话时区进行显示，而 TIMESTAMP_TZ 会保留原始偏移，适用于审计或副本场景。

示例

DATE

CREATE TABLE events (event_date DATE);
INSERT INTO events VALUES ('2024-01-15'), ('2024-12-31');
SELECT * FROM events;

结果：

┌────────────┐
│ event_date │
├────────────┤
│ 2024-01-15 │
│ 2024-12-31 │
└────────────┘

TIMESTAMP

CREATE TABLE meetings (
  meeting_id INT,
  meeting_time TIMESTAMP
);

INSERT INTO meetings VALUES (1, '2024-01-15 14:00:00+08:00');

SETTINGS (timezone = 'UTC')
SELECT meeting_id, meeting_time FROM meetings;

SETTINGS (timezone = 'America/New_York')
SELECT meeting_id, meeting_time FROM meetings;

结果（timezone = 'UTC'）：

┌────────────┬──────────────────────┐
│ meeting_id │ meeting_time         │
├────────────┼──────────────────────┤
│          1 │ 2024-01-15T06:00:00 │
└────────────┴──────────────────────┘

结果（timezone = 'America/New_York'）：

┌────────────┬──────────────────────┐
│ meeting_id │ meeting_time         │
├────────────┼──────────────────────┤
│          1 │ 2024-01-15T01:00:00 │
└────────────┴──────────────────────┘

TIMESTAMP_TZ

CREATE TABLE system_logs (
  log_id INT,
  log_time TIMESTAMP_TZ
);

INSERT INTO system_logs VALUES
  (1, '2024-01-15 14:00:00+08:00'),
  (2, '2024-01-15 06:00:00+00:00'),
  (3, '2024-01-15 01:00:00-05:00');

SETTINGS (timezone = 'UTC')
SELECT log_id, TO_STRING(log_time) AS log_time FROM system_logs;

SETTINGS (timezone = 'Asia/Shanghai')
SELECT log_id, TO_STRING(log_time) AS log_time FROM system_logs;

结果（timezone = 'UTC'）：

┌────────┬────────────────────────────────────────────┐
│ log_id │ log_time                                   │
├────────┼────────────────────────────────────────────┤
│      1 │ 2024-01-15 14:00:00.000000 +0800           │
│      2 │ 2024-01-15 06:00:00.000000 +0000           │
│      3 │ 2024-01-15 01:00:00.000000 -0500           │
└────────┴────────────────────────────────────────────┘

结果（timezone = 'Asia/Shanghai'）：

┌────────┬────────────────────────────────────────────┐
│ log_id │ log_time                                   │
├────────┼────────────────────────────────────────────┤
│      1 │ 2024-01-15 14:00:00.000000 +0800           │
│      2 │ 2024-01-15 06:00:00.000000 +0000           │
│      3 │ 2024-01-15 01:00:00.000000 -0500           │
└────────┴────────────────────────────────────────────┘

偏移是存储值的一部分，因此显示结果不会发生变化。

选择合适的类型

如果只需要日历日期而不包含一天中的具体时间，请使用 DATE。
如果希望不同会话以各自本地时区显示同一时刻，请使用 TIMESTAMP。
如果必须保留输入时的偏移以满足合规或调试需求，请使用 TIMESTAMP_TZ。

夏令时调整

启用 enable_dst_hour_fix 后，当夏令时导致一天中的某些小时被跳过时，TiDB Cloud Lake 会自动将缺失的小时向后滚动到下一个有效时间。

SET enable_dst_hour_fix = 1;

SETTINGS (timezone = 'America/Toronto')
SELECT to_datetime('2024-03-10 02:01:00');

结果：

┌────────────────────────────────────┐
│ to_datetime('2024-03-10 02:01:00') │
├────────────────────────────────────┤
│ 2024-03-10T03:01:00                │
└────────────────────────────────────┘

如果你更希望对缺失的小时直接报错，可以使用 SET enable_dst_hour_fix = 0 恢复默认行为。

处理无效值

超出支持范围的日期会自动钳制到其最小值。

SELECT
  ADD_DAYS(TO_DATE('9999-12-31'), 1)         AS overflow_date,
  SUBTRACT_MINUTES(TO_DATE('1000-01-01'), 1) AS underflow_timestamp;

结果：

┌───────────────┬──────────────────────────┐
│ overflow_date │ underflow_timestamp      │
├───────────────┼──────────────────────────┤
│ 0001-01-01    │ 0999-12-31T18:41:28      │
└───────────────┴──────────────────────────┘

这些值会回绕到可表示的最小日期或时间戳，而不是报错。

格式化日期和时间

TO_DATE 和 TO_TIMESTAMP 等函数支持显式格式字符串。你可以通过调整 date_format_style 和 week_start 来控制它们如何解析或渲染值。

Date Format Styles

使用 date_format_style 可以在两种格式词汇体系之间切换：

MySQL（默认）使用 %Y、%m、%d 这类说明符。
Oracle 使用 YYYY、MM、DD 这类说明符，以匹配 ANSI 风格的掩码。

-- Oracle-style mask
SETTINGS (date_format_style = 'Oracle')
SELECT to_string('2024-04-05'::DATE, 'YYYY-MM-DD');

结果（Oracle）：

┌──────────────────────────────────────┐
│ to_string('2024-04-05'::DATE, 'YYYY-MM-DD') │
├──────────────────────────────────────┤
│ 2024-04-05                           │
└──────────────────────────────────────┘

-- Back to MySQL-style mask
SETTINGS (date_format_style = 'MySQL')
SELECT to_string('2024-04-05'::DATE, '%Y-%m-%d');

结果（MySQL）：

┌──────────────────────────────────────┐
│ to_string('2024-04-05'::DATE, '%Y-%m-%d') │
├──────────────────────────────────────┤
│ 2024-04-05                           │
└──────────────────────────────────────┘

Week Start Configuration

week_start 用于定义一周从星期几开始，适用于使用 WEEK 精度时的 DATE_TRUNC 或 TRUNC 等函数。

SETTINGS (week_start = 0) SELECT DATE_TRUNC(WEEK, to_date('2024-04-05')); -- Sunday
SETTINGS (week_start = 1) SELECT DATE_TRUNC(WEEK, to_date('2024-04-05')); -- Monday

结果（week_start = 0）：

┌────────────────────────────────┐
│ DATE_TRUNC(WEEK, TO_DATE('2024-04-05')) │
├────────────────────────────────┤
│ 2024-03-31                     │
└────────────────────────────────┘

结果（week_start = 1）：

┌────────────────────────────────┐
│ DATE_TRUNC(WEEK, TO_DATE('2024-04-05')) │
├────────────────────────────────┤
│ 2024-04-01                     │
└────────────────────────────────┘

MySQL Format Specifiers

为了处理日期和时间格式化，TiDB Cloud Lake 使用 chrono::format::strftime 模块，这是 Rust 中 chrono 库提供的标准模块。该模块可以对日期和时间的格式进行精确控制。以下内容摘自 https://docs.rs/chrono/latest/chrono/format/strftime/index.html：

说明符	示例	描述
		日期说明符：
%Y	2001	完整的前推公历年份，左侧补零至 4 位。chrono 支持从 -262144 到 262143 的年份。注意：对于公元前 1 年之前或公元 9999 年之后的年份，需要带前导符号（+/-）。
%C	20	前推公历年份除以 100 的结果，左侧补零至 2 位。
%y	01	前推公历年份对 100 取模的结果，左侧补零至 2 位。
%m	07	月份编号（01–12），左侧补零至 2 位。
%b	Jul	月份简称。始终为 3 个字母。
%B	July	月份全称。解析时也接受对应的简称。
%h	Jul	与 %b 相同。
%d	08	日期编号（01–31），左侧补零至 2 位。
%e	8	与 %d 相同，但使用空格填充。等同于 %_d。
%a	Sun	星期简称。始终为 3 个字母。
%A	Sunday	星期全称。解析时也接受对应的简称。
%w	0	星期日 = 0，星期一 = 1，…，星期六 = 6。
%u	7	星期一 = 1，星期二 = 2，…，星期日 = 7。（ISO 8601）
%U	28	以星期日为一周起始日的周编号（00–53），左侧补零至 2 位。
%W	27	与 %U 相同，但第 1 周改为从该年的第一个星期一开始。
%G	2001	与 %Y 相同，但使用 ISO 8601 周日期中的年份编号。
%g	01	与 %y 相同，但使用 ISO 8601 周日期中的年份编号。
%V	27	与 %U 相同，但使用 ISO 8601 周日期中的周编号（01–53）。
%j	189	一年中的第几天（001–366），左侧补零至 3 位。
%D	07/08/01	月-日-年格式。等同于 %m/%d/%y。
%x	07/08/01	区域设置的日期表示形式（例如 12/31/99）。
%F	2001-07-08	年-月-日格式（ISO 8601）。等同于 %Y-%m-%d。
%v	8-Jul-2001	日-月-年格式。等同于 %e-%b-%Y。
		时间说明符：
%H	00	小时编号（00–23），左侧补零至 2 位。
%k	0	与 %H 相同，但使用空格填充。等同于 %_H。
%I	12	12 小时制中的小时编号（01–12），左侧补零至 2 位。
%l	12	与 %I 相同，但使用空格填充。等同于 %_I。
%P	am	12 小时制中的 am 或 pm。
%p	AM	12 小时制中的 AM 或 PM。
%M	34	分钟编号（00–59），左侧补零至 2 位。
%S	60	秒编号（00–60），左侧补零至 2 位。
%f	026490000	自上一整秒以来的小数秒部分（以纳秒计）。TiDB Cloud Lake 建议优先将 Integer 字符串转换为 Integer，而不是使用此说明符。示例请参见将整数转换为时间戳。
%.f	.026490	与 .%f 类似，但左对齐。这些格式都会消耗前导点号。
%.3f	.026	与 .%f 类似，但左对齐，并固定长度为 3。
%.6f	.026490	与 .%f 类似，但左对齐，并固定长度为 6。
%.9f	.026490000	与 .%f 类似，但左对齐，并固定长度为 9。
%3f	026	与 %.3f 类似，但不带前导点号。
%6f	026490	与 %.6f 类似，但不带前导点号。
%9f	026490000	与 %.9f 类似，但不带前导点号。
%R	00:34	时:分格式。等同于 %H:%M。
%T	00:34:60	时:分:秒格式。等同于 %H:%M:%S。
%X	00:34:60	区域设置的时间表示形式（例如 23:13:48）。
%r	12:34:60 AM	12 小时制的时:分:秒格式。等同于 %I:%M:%S %p。
		时区说明符：
%Z	ACST	本地时区名称。解析时会跳过所有非空白字符。
%z	+0930	本地时间相对于 UTC 的偏移（其中 UTC 为 +0000）。
%:z	+09:30	与 %z 相同，但带冒号。
%::z	+09:30:00	本地时间相对于 UTC 的偏移，包含秒。
%:::z	+09	本地时间相对于 UTC 的偏移，不包含分钟。
%#z	+09	仅用于解析：与 %z 相同，但允许分钟部分缺失或存在。
		日期和时间说明符：
%c	Sun Jul 8 00:34:60 2001	区域设置的日期和时间表示形式（例如 Thu Mar 3 23:05:25 2005）。
%+	2001-07-08T00:34:60.026490+09:30	ISO 8601 / RFC 3339 日期和时间格式。
%s	994518299	UNIX 时间戳，即自 1970-01-01 00:00 UTC 以来的秒数。TiDB Cloud Lake 建议优先将 Integer 字符串转换为 Integer，而不是使用此说明符。示例请参见将整数转换为时间戳。
		特殊说明符：
%t		字面量制表符（\t）。
%n		字面量换行符（\n）。
%%		字面量百分号。

可以覆盖数值说明符 %? 的默认填充行为。其他说明符不允许这样做，否则会导致 BAD_FORMAT 错误。

修饰符	描述
%-?	禁用任何填充，包括空格和零。（例如 %j = 012，%-j = 12）
%_?	使用空格作为填充。（例如 %j = 012，%_j = 12）
%0?	使用零作为填充。（例如 %e = 9，%0e = 09）

%C, %y：这里使用向下取整除法，因此公元前 100 年（年份编号 -99）将分别输出 -1 和 99。
%U：第 1 周从该年的第一个星期日开始。在第一个星期日之前的日期可能属于第 0 周。
%G, %g, %V：第 1 周是该年中至少包含 4 天的第一周。不存在第 0 周，因此应与 %G 或 %g 搭配使用。
%S：它会考虑闰秒，因此 60 是可能的。
%f, %.f, %.3f, %.6f, %.9f, %3f, %6f, %9f：
默认的 %f 是右对齐，并且始终左侧补零到 9 位，以兼容 glibc 等实现，因此它始终表示自上一整秒以来的纳秒数。例如，距离上一秒过去 7ms 时会输出 007000000，而解析 7000000 也会得到相同结果。
变体 %.f 是左对齐，并根据精度输出 0、3、6 或 9 位小数。例如，距离上一秒过去 70ms 时，使用 %.f 会输出 .070（注意：不是 .07）；解析 .07、.070000 等也会得到相同结果。注意，如果小数部分为零，或者下一个字符不是 .，则它们可能不会输出或读取任何内容。
变体 %.3f、%.6f 和 %.9f 是左对齐，并根据 f 前面的数字输出 3、6 或 9 位小数。例如，距离上一秒过去 70ms 时，使用 %.3f 会输出 .070（注意：不是 .07）；解析 .07、.070000 等也会得到相同结果。注意，如果小数部分为零，或者下一个字符不是 .，则它们在读取时可能不会读取任何内容；但在输出时会按指定长度输出。
变体 %3f、%6f 和 %9f 是左对齐，并根据 f 前面的数字输出 3、6 或 9 位小数，但不带前导点号。例如，距离上一秒过去 70ms 时，使用 %3f 会输出 070（注意：不是 07）；解析 07、070000 等也会得到相同结果。注意，如果小数部分为零，则它们在读取时可能不会读取任何内容。
%Z：不会根据解析出的数据来填充偏移，也不会对其进行校验。时区会被完全忽略。这与 glibc 的 strptime 对该格式代码的处理方式类似。
无法可靠地将缩写转换为偏移，例如 CDT 既可能表示 Central Daylight Time（北美中部夏令时），也可能表示 China Daylight Time（中国夏令时）。
%+：等同于 %Y-%m-%dT%H:%M:%S%.f%:z，也就是说，秒的小数部分会输出 0、3、6 或 9 位，时区偏移中带有冒号。
该格式还支持使用 Z 或 UTC 替代 %:z。它们都等价于 +00:00。
注意，所有的 T、Z 和 UTC 在解析时都不区分大小写。
典型的 strftime 实现对该说明符的格式定义各不相同（并且依赖区域设置）。虽然 Chrono 对 %+ 的格式更稳定，但如果你希望精确控制输出，最好避免使用该说明符。
%s：该说明符不进行填充，并且可以为负数。对于 Chrono 而言，它只考虑非闰秒，因此与 ISO C strftime 的行为略有不同。

Oracle 格式说明符

当 date_format_style 设置为 'Oracle' 时，支持以下格式说明符：

Oracle 格式	描述	示例输出（对应 '2024-04-05 14:30:45.123456'）
YYYY	4 位年份	2024
YY	2 位年份	24
MMMM	完整月份名称	April
MON	缩写月份名称	Apr
MM	月份数字 (01-12)	04
DD	月中的日期 (01-31)	05
DY	缩写星期名称	Fri
HH24	一天中的小时 (00-23)	14
HH12	一天中的小时 (01-12)	02
AM/PM	上下午指示符	PM
MI	分钟 (00-59)	30
SS	秒 (00-59)	45
FF	小数秒	123456
UUUU	ISO 周编号年份	2024
TZH:TZM	带冒号的时区小时和分钟	+08:00
TZH	时区小时	+08

以下示例使用相同的数据对比 MySQL 和 Oracle 格式风格：

-- MySQL format style (default)
SELECT to_string('2022-12-25'::DATE, '%m/%d/%Y');

┌────────────────────────────────┐
│ to_string('2022-12-25', '%m/%d/%Y') │
├────────────────────────────────┤
│ 12/25/2022                     │
└────────────────────────────────┘

-- Oracle format style (same data as MySQL example above)
SETTINGS (date_format_style = 'Oracle')
SELECT to_string('2022-12-25'::DATE, 'MM/DD/YYYY');

┌────────────────────────────────┐
│ to_string('2022-12-25', 'MM/DD/YYYY') │
├────────────────────────────────┤
│ 12/25/2022                     │
└────────────────────────────────┘