跳到主要内容

日期和时间

引入或更新于: v1.2.745

日期和时间数据类型

名称别名存储大小精度最小值最大值格式
DATE4 字节0001-01-019999-12-31YYYY-MM-DD
TIMESTAMPDATETIME8 字节微秒0001-01-01 00:00:00.0000009999-12-31 23:59:59.999999 UTCYYYY-MM-DD hh:mm:ss[.fraction],支持最多 6 位微秒精度

示例

CREATE TABLE test_dt
(
date DATE,
ts TIMESTAMP
);
DESC test_dt;

结果:

┌────────────────────────────────────────────────┐
│ Field │ Type │ Null │ Default │ Extra │
├────────┼───────────┼────────┼─────────┼────────┤
│ date │ DATE │ YES │ NULL │ │
│ ts │ TIMESTAMP │ YES │ NULL │ │
└────────────────────────────────────────────────┘

TIMESTAMP 值可以选择性地包含尾随的小数秒部分,精度最高可达微秒 (6 位数字)。

-- 向表中插入值
INSERT INTO test_dt
VALUES
('2022-04-07', '2022-04-07 01:01:01.123456'),
('2022-04-08', '2022-04-08 01:01:01');

SELECT *
FROM test_dt;

结果:

┌─────────────────────────────────────────────┐
│ date │ ts │
├────────────────┼────────────────────────────┤
│ 2022-04-07 │ 2022-04-07 01:01:01.123456 │
│ 2022-04-08 │ 2022-04-08 01:01:01 │
└─────────────────────────────────────────────┘

Databend 识别多种格式的 TIMESTAMP 值。

-- 创建表来测试不同的时间戳格式
CREATE TABLE test_formats (
id INT,
a TIMESTAMP
);

-- 插入不同时间戳格式的值
INSERT INTO test_formats
VALUES
(1, '2022-01-01 02:00:11'),
(2, '2022-01-02T02:00:22'),
(3, '2022-02-02T04:00:03+00:00'),
(4, '2022-02-03');
SELECT *
FROM test_formats;

结果:

┌───────────────────────────────────────┐
│ id │ a │
├─────────────────┼─────────────────────┤
│ 1 │ 2022-01-01 02:00:11 │
│ 2 │ 2022-01-02 02:00:22 │
│ 3 │ 2022-02-02 04:00:03 │
│ 4 │ 2022-02-03 00:00:00 │
└───────────────────────────────────────┘

Databend 会根据您当前的时区自动调整并显示 TIMESTAMP 值。

-- 创建表来测试带时区调整的时间戳值
CREATE TABLE test_tz (
id INT,
t TIMESTAMP
);

-- 设置时区为 UTC
SET timezone = 'UTC';

-- 插入考虑不同时区的时间戳值
INSERT INTO test_tz
VALUES
(1, '2022-02-03T03:00:00'),
(2, '2022-02-03T03:00:00+08:00'),
(3, '2022-02-03T03:00:00-08:00'),
(4, '2022-02-03'),
(5, '2022-02-03T03:00:00+09:00'),
(6, '2022-02-03T03:00:00+06:00');
SELECT *
FROM test_tz;

结果:

┌───────────────────────────────────────┐
│ id │ t │
├─────────────────┼─────────────────────┤
│ 1 │ 2022-02-03 03:00:00 │
│ 2 │ 2022-02-02 19:00:00 │
│ 3 │ 2022-02-03 11:00:00 │
│ 4 │ 2022-02-03 00:00:00 │
│ 5 │ 2022-02-02 18:00:00 │
│ 6 │ 2022-02-02 21:00:00 │
└───────────────────────────────────────┘
-- 将时区更改为 Asia/Shanghai
SET timezone = 'Asia/Shanghai';

-- 使用新的时区设置从表中选择数据
SELECT *
FROM test_tz;

结果:

┌───────────────────────────────────────┐
│ id │ t │
├─────────────────┼─────────────────────┤
│ 1 │ 2022-02-03 11:00:00 │
│ 2 │ 2022-02-03 03:00:00 │
│ 3 │ 2022-02-03 19:00:00 │
│ 4 │ 2022-02-03 08:00:00 │
│ 5 │ 2022-02-03 02:00:00 │
│ 6 │ 2022-02-03 05:00:00 │
└───────────────────────────────────────┘

函数

参见 日期时间函数

处理夏令时调整

在某些地区,会实行夏令时。在夏令时开始的那一天,时钟会向前调整一小时。Databend 通过 enable_dst_hour_fix 设置来管理夏令时调整。启用后,Databend 会自动将时间向前推进一小时 (例如,2:10 AM 将被处理为 3:10 AM)。

例如,多伦多的夏令时于 2024 年 3 月 10 日凌晨 2:00 开始。因此,当天凌晨 2:00 到 3:00 之间的时间实际上不存在。Databend 依赖 Chrono 来确定每个时区的夏令时。如果提供了这个范围内的时间,Databend 将返回错误:

SET timezone = 'America/Toronto';

SELECT to_datetime('2024-03-10 02:01:00');
error: APIError: ResponseError with 1006: cannot parse to type `TIMESTAMP`. BadArguments. Code: 1006, Text = unexpected argument. while evaluating function `to_timestamp('2024-03-10 02:01:00')` in expr `to_timestamp('2024-03-10 02:01:00')`

要修复此类错误,您可以启用 enable_dst_hour_fix 设置来将时间向前推进一小时:

SET enable_dst_hour_fix = 1;

SELECT to_datetime('2024-03-10 02:01:00');

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

处理无效值

Databend 会自动将无效的 Date 或 Timestamp 值转换为其最小有效等价值,日期为 1000-01-01,时间戳为 1000-01-01 00:00:00,确保在处理超出范围或格式错误的日期和时间戳时保持一致性。

示例:

-- 尝试在最大日期上加一天,超出有效范围。
-- 结果:返回 DateMIN (1000-01-01) 而不是错误。
SELECT ADD_DAYS(TO_DATE('9999-12-31'), 1);

┌────────────────────────────────────┐
│ add_days(to_date('9999-12-31'), 1)
├────────────────────────────────────┤
1000-01-01
└────────────────────────────────────┘
-- 尝试从最小日期减去一分钟,这将是无效的。
-- 结果:返回 DateMIN (1000-01-01 00:00:00),确保结果的稳定性。
SELECT SUBTRACT_MINUTES(TO_DATE('1000-01-01'), 1);

┌────────────────────────────────────────────┐
│ subtract_minutes(to_date('1000-01-01'), 1)
├────────────────────────────────────────────┤
1000-01-01 00:00:00
└────────────────────────────────────────────┘

格式化日期和时间

在 Databend 中,某些日期和时间函数如 TO_DATETO_TIMESTAMP 需要您指定日期和时间值的所需格式。

日期格式样式

Databend 支持两种日期格式样式,可以使用 date_format_style 设置进行选择:

  • MySQL (默认):使用 MySQL 兼容的格式说明符,如 %Y%m%d 等。
  • Oracle:使用格式说明符,如 YYYYMMDD 等,遵循标准化格式以确保与常见 SQL 标准的兼容性。

要在格式样式之间切换,请使用 date_format_style 设置:

-- 设置 Oracle 样式日期格式
SETTINGS (date_format_style = 'Oracle') SELECT to_string('2024-04-05'::DATE, 'YYYY-MM-DD');

-- 设置 MySQL 日期格式样式 (默认)
SETTINGS (date_format_style = 'MySQL') SELECT to_string('2024-04-05'::DATE, '%Y-%m-%d');

周开始配置

Databend 提供了 week_start 设置,用于定义哪一天被视为一周的第一天:

  • week_start = 1 (默认):周一被视为一周的第一天
  • week_start = 0:周日被视为一周的第一天

此设置会影响与周相关的日期函数,如 DATE_TRUNCTRUNC,当使用 WEEK 作为精度参数时:

-- 设置周日为一周的第一天
SETTINGS (week_start = 0) SELECT DATE_TRUNC(WEEK, to_date('2024-04-05'));

-- 设置周一为一周的第一天 (默认)
SETTINGS (week_start = 1) SELECT DATE_TRUNC(WEEK, to_date('2024-04-05'));

MySQL 格式说明符

为了处理日期和时间格式化,Databend 使用了 chrono::format::strftime 模块,这是 Rust 中 chrono 库提供的标准模块。该模块能够精确控制日期和时间的格式化。以下内容摘录自 https://docs.rs/chrono/latest/chrono/format/strftime/index.html

规格示例描述
日期说明符:
%Y2001完整的前推格里高利历年份,零填充至 4 位数字。chrono 支持从 -262144 到 262143 的年份。注意:公元前 1 年之前或公元 9999 年之后的年份需要初始符号 (+/-)。
%C20前推格里高利历年份除以 100,零填充至 2 位数字。
%y01前推格里高利历年份模 100,零填充至 2 位数字。
%m07月份数字 (01–12),零填充至 2 位数字。
%bJul缩写月份名称。始终为 3 个字母。
%BJuly完整月份名称。在解析时也接受相应的缩写。
%hJul与 %b 相同。
%d08日期数字 (01–31),零填充至 2 位数字。
%e8与 %d 相同但使用空格填充。与 %_d 相同。
%aSun缩写星期名称。始终为 3 个字母。
%ASunday完整星期名称。在解析时也接受相应的缩写。
%w0星期日 = 0,星期一 = 1,…,星期六 = 6。
%u7星期一 = 1,星期二 = 2,…,星期日 = 7。(ISO 8601)
%U28以星期日开始的周数 (00–53),零填充至 2 位数字。
%W27与 %U 相同,但第 1 周从该年的第一个星期一开始。
%G2001与 %Y 相同但使用 ISO 8601 周日期中的年份数字。
%g01与 %y 相同但使用 ISO 8601 周日期中的年份数字。
%V27与 %U 相同但使用 ISO 8601 周日期中的周数 (01–53)。
%j189一年中的第几天 (001–366),零填充至 3 位数字。
%D07/08/01月-日-年格式。与 %m/%d/%y 相同。
%x07/08/01本地日期表示 (例如,12/31/99)。
%F2001-07-08年-月-日格式 (ISO 8601)。与 %Y-%m-%d 相同。
%v8-Jul-2001日-月-年格式。与 %e-%b-%Y 相同。
时间说明符:
%H00小时数字 (00–23),零填充至 2 位数字。
%k0与 %H 相同但使用空格填充。与 %_H 相同。
%I1212 小时制中的小时数字 (01–12),零填充至 2 位数字。
%l12与 %I 相同但使用空格填充。与 %_I 相同。
%Pam12 小时制中的 am 或 pm。
%pAM12 小时制中的 AM 或 PM。
%M34分钟数字 (00–59),零填充至 2 位数字。
%S60秒数字 (00–60),零填充至 2 位数字。
%f026490000自上一个整秒以来的小数秒 (以纳秒为单位)。Databend 建议先将 Integer 字符串转换为 Integer,而不是使用此说明符。请参阅将 Integer 转换为 Timestamp 示例。
%.f.026490与 .%f 类似但左对齐。这些都会消耗前导点。
%.3f.026与 .%f 类似但左对齐且固定长度为 3。
%.6f.026490与 .%f 类似但左对齐且固定长度为 6。
%.9f.026490000与 .%f 类似但左对齐且固定长度为 9。
%3f026与 %.3f 类似但没有前导点。
%6f026490与 %.6f 类似但没有前导点。
%9f026490000与 %.9f 类似但没有前导点。
%R00:34小时-分钟格式。与 %H:%M 相同。
%T00:34:60小时-分钟-秒格式。与 %H:%M:%S 相同。
%X00:34:60本地时间表示 (例如,23:13:48)。
%r12:34:60 AM12 小时制中的小时-分钟-秒格式。与 %I:%M:%S %p 相同。
时区说明符:
%ZACST本地时区名称。在解析期间跳过所有非空白字符。
%z+0930从本地时间到 UTC 的偏移量 (UTC 为 +0000)。
%:z+09:30与 %z 相同但带有冒号。
%::z+09:30:00从本地时间到 UTC 的偏移量,包含秒。
%:::z+09从本地时间到 UTC 的偏移量,不包含分钟。
%#z+09仅解析:与 %z 相同但允许分钟缺失或存在。
日期和时间说明符:
%cSun Jul 8 00:34:60 2001本地日期和时间 (例如,Thu Mar 3 23:05:25 2005)。
%+2001-07-08T00:34:60.026490+09:30ISO 8601 / RFC 3339 日期和时间格式。
%s994518299UNIX 时间戳,自 1970-01-01 00:00 UTC 以来的秒数。Databend 建议先将 Integer 字符串转换为 Integer,而不是使用此说明符。请参阅将 Integer 转换为 Timestamp 示例。
特殊说明符:
%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 是右对齐的,并且为了与 glibc 和其他库的兼容性,总是用零填充到 9 位数字,因此它总是计算自上一个整秒以来的纳秒数。例如,距离上一秒 7ms 后将打印 007000000,解析 7000000 将产生相同的结果。

    变体 %.f 是左对齐的,根据精度打印 0、3、6 或 9 位小数。例如,在 %.f 下距离上一秒 70ms 后将打印 .070 (注意: 不是 .07),解析 .07、.070000 等将产生相同的结果。注意,如果小数部分为零或下一个字符不是 . ,它们可能不打印或读取任何内容。

    变体 %.3f、%.6f 和 %.9f 是左对齐的,根据 f 前面的数字打印 3、6 或 9 位小数。例如,在 %.3f 下距离上一秒 70ms 后将打印 .070 (注意: 不是 .07),解析 .07、.070000 等将产生相同的结果。注意,如果小数部分为零或下一个字符不是 . ,它们可能读取不到任何内容,但会按指定长度打印。

    变体 %3f、%6f 和 %9f 是左对齐的,根据 f 前面的数字打印 3、6 或 9 位小数,但没有前导点。例如,在 %3f 下距离上一秒 70ms 后将打印 070 (注意: 不是 07),解析 07、070000 等将产生相同的结果。注意,如果小数部分为零,它们可能读取不到任何内容。

  • %Z: 偏移量不会从解析的数据中填充,也不会被验证。时区完全被忽略。类似于 glibc strptime 对此格式代码的处理。

    无法可靠地从缩写转换为偏移量,例如 CDT 可能表示中部夏令时 (北美) 或中国夏令时。

  • %+: 与 %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')
YYYY4 位年份2024
YY2 位年份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
UUUUISO 周编号年份2024
TZH:TZM带冒号的时区小时和分钟+08:00
TZH时区小时+08

使用相同数据比较 MySQL 和 Oracle 格式样式的示例:

-- MySQL 格式样式 (默认)
SELECT to_string('2022-12-25'::DATE, '%m/%d/%Y');

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

-- Oracle 格式样式 (与上面 MySQL 示例相同的数据)
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
└────────────────────────────────┘