日期和时间输入和输出格式

本主题内容：

Snowflake 如何确定要使用的输入和输出格式

Snowflake 使用以下内容来确定用于日期、时间和时间戳的输入和输出格式：

日期、时间和时间戳的会话参数

一组会话参数可确定日期、时间和时间戳数据如何传入和传出 Snowflake，以及支持时区的时间和时间戳格式中使用的时区。

可以在账户、用户和会话级别设置相应参数。执行 SHOW PARAMETERS 命令可查看应用于当前会话中所有操作的当前参数设置。

输入格式

以下参数定义了 DML （包括 COPY、INSERT 和 MERGE 操作）可识别的日期、时间和时间戳格式：

三个参数的默认值均为 AUTO。当参数值设置为 AUTO 时，Snowflake 会尝试将任何输入表达式中的日期、时间或时间戳字符串与 AUTO 检测支持的格式 中列出的格式进行匹配。

  • 如果找到匹配的格式，Snowflake 将接受该字符串。

  • 如果未找到匹配的格式，Snowflake 将返回错误。

输出格式

以下参数定义了 Snowflake 输出的日期和时间格式：

此外，以下参数将 TIMESTAMP 数据类型别名映射到三个 TIMESTAMP_* 变体之一：

时区

以下参数确定了时区：

用于加载/卸载日期、时间和时间戳的文件格式选项

除了输入和输出格式参数之外，Snowflake 提供了三种文件格式选项，可在将数据加载到 Snowflake 表中或从 Snowflake 表中卸载数据时使用：

  • DATE_FORMAT

  • TIME_FORMAT

  • TIMESTAMP_FORMAT

这些选项可以直接在 COPY 命令中指定，也可以在 COPY 命令引用的指定暂存区或文件格式对象中指定。指定后，这些选项将替换相应的输入格式（加载数据时）或输出格式（卸载数据时）。

数据加载

在数据加载中使用时，这些选项可指定暂存数据文件中日期、时间和时间戳字符串的格式。这些选项将替换 DATE_INPUT_FORMAT、TIME_INPUT_FORMAT 或 TIMESTAMP_INPUT_FORMAT 参数设置。

所有这些选项的默认值均为 AUTO，这意味着 COPY INTO <table> 命令会尝试将暂存数据文件中的所有日期和时间戳字符串与 AUTO 检测支持的格式 中列出的格式进行匹配。

  • 如果找到匹配的格式，Snowflake 将接受该字符串。

  • 如果未找到匹配的格式，Snowflake 将返回错误，然后执行为 ON_ERROR 复制选项指定的操作。

警告

Snowflake 支持对大多数常见的日期、时间和时间戳格式（见下表）进行自动检测；但是，某些格式可能会产生不明确的结果，这可能导致 Snowflake 在使用 AUTO 加载数据时应用不正确的格式。

为了保证正确加载数据，Snowflake 强烈 建议对数据加载的文件格式选项进行显式设置。

数据卸载

在数据卸载中使用时，这些选项可指定卸载到指定暂存区中的文件所应用的日期、时间和时间戳格式。

所有这些选项的默认值均为 AUTO，这意味着 Snowflake 应用以下参数中指定的格式：

  • DATE_OUTPUT_FORMAT

  • TIME_OUTPUT_FORMAT

  • TIMESTAMP_*_OUTPUT_FORMAT （取决于 TIMESTAMP_TYPE_MAPPING 设置）

关于输入和输出格式中使用的元素

在 参数、文件格式选项 和 转换函数 中指定的输入和输出格式中，可以使用下表列出的元素。

接下来的各节 还将使用这些元素来介绍 Snowflake 可自动识别的格式。

格式元素

描述

YYYY

四位数年份。

YY

两位数年份，由 TWO_DIGIT_CENTURY_START 会话参数控制，例如，当设置为 1980 时，79 和 80 这两个值将分别解析为 2079 和 1980。

MM

两位数月份（01 = 一月，以此类推）。

MON

月份名称的全称或缩写。

MMMM

月份名称的全称。

DD

两位数日期（01 至 31）。

DY

星期的缩写。

HH24

两位数小时（00 至 23）。不能 指定 AM / PM。

HH12

两位数小时（01 至 12）。可以指定 AM/PM。

AM、PM

上午 (am)/下午 (pm)。仅可用于 HH12 （不能 用于 HH24）。

MI

两位数分钟（00 至 59）。

SS

两位数秒钟（00 至 59）。

FF[0-9]

精度为 0（秒）至 9（纳秒）的小数秒，例如 FF、FF0、FF3、FF9。指定 FF 等同于 FF9 （纳秒）。

TZH:TZM、TZHTZM、TZH

时区小时和分钟，从 UTC 开始偏移。可以用 +/- 作为符号前缀。

UUUU

四位数年份，格式为 ISO (link removed)，负数为 BCE 年份。

备注

  • 当使用仅日期格式时，关联时间假定为当天的午夜。

  • 格式中双引号之间或上述元素以外的任何内容都会被解析/格式化，但不会被解释。

  • 有关有效范围、位数和最佳实践的更多详细信息，请参阅 有关使用日期、时间和时间戳格式的其他信息。

AUTO 检测支持的格式

如果有指示，Snowflake 会自动检测和处理日期、时间和时间戳输入字符串的特定格式。以下各节介绍了支持的格式：

注意

某些字符串可以匹配多种格式。例如，“07-04-2016”与 MM-DD-YYYY 和 DD-MM-YYYY 均兼容，但在每种格式中却具有不同的含义（7 月 4 日与 4 月 7 日）。找到匹配的格式 并不能 保证字符串按用户预期进行解析。

尽管自动检测日期格式很方便，但它增加了日期被错误解释的可能性。Snowflake 强烈建议显式指定格式，而不是依赖于自动日期检测。

日期格式

有关以下格式中使用的元素的说明，请参阅 关于输入和输出格式中使用的元素。

格式

示例

备注

ISO 日期格式
YYYY-MM-DD

2013-04-28

其他日期格式
DD-MON-YYYY

17-DEC-1980
MM/DD/YYYY

12/17/1980

在加载或操作常用欧洲格式的日期（即 DD/MM/YYYY）时，可能会生成不正确的日期。例如，05/02/2013 可能被解释为 2013 年 5 月 2 日，而不是 2013 年 2 月 5 日。

使用 AUTO 日期格式时，破折号和斜线不可互换。斜线表示 MM/DD/YYYY 格式，破折号表示 YYYY-MM-DD 格式。诸如“2019/01/02”或“01-02-2019”之类的字符串不会按照您的预期进行解释。

时间格式

有关以下格式中使用的元素的说明，请参阅 关于输入和输出格式中使用的元素。

格式

示例

备注

ISO 时间格式
HH24:MI:SS.FFTZH:TZM

20:57:01.123456789+07:00
HH24:MI:SS.FF

20:57:01.123456789
HH24:MI:SS

20:57:01
HH24:MI

20:57

互联网 (RFC) 时间格式
HH12:MI:SS.FF AM

07:57:01.123456789 AM
HH12:MI:SS AM

04:01:07 AM
HH12:MI AM

04:01 AM

AM 格式元素允许使用带有 AM 或 PM 的值。

备注

AM 格式元素应仅用于 HH12 （不能用于 HH24）。

如果时间或时间戳字符串中的数字之后紧跟时区偏移量（例如“0800”），则时区偏移量必须以 + 或 - 开头。当小数秒或时区偏移量未达到允许的最大位数时，该符号可防止歧义。例如，如果小数秒的末位数字和时区的首位数字之间没有分隔符，则时间“04:04:04.321200”中的“1”既可能是小数秒的末位数字（例如 321 毫秒）或时区偏移量的首位数字（例如比 UTC 早 12 小时）。

时间戳格式

有关以下格式中使用的元素的说明，请参阅 关于输入和输出格式中使用的元素。

格式

示例

备注

ISO 时间戳格式
YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM

2013-04-28T20:57:01.123456789+07:00

T 周围的双引号是可选的（有关详细信息，请参阅此表后面的提示）。
YYYY-MM-DD HH24:MI:SS.FFTZH:TZM

2013-04-28 20:57:01.123456789+07:00
YYYY-MM-DD HH24:MI:SS.FFTZH

2013-04-28 20:57:01.123456789+07
YYYY-MM-DD HH24:MI:SS.FF TZH:TZM

2013-04-28 20:57:01.123456789 +07:00
YYYY-MM-DD HH24:MI:SS.FF TZHTZM

2013-04-28 20:57:01.123456789 +0700
YYYY-MM-DD HH24:MI:SS TZH:TZM

2013-04-28 20:57:01 +07:00
YYYY-MM-DD HH24:MI:SS TZHTZM

2013-04-28 20:57:01 +0700
YYYY-MM-DD"T"HH24:MI:SS.FF

2013-04-28T20:57:01.123456

T 周围的双引号是可选的（有关详细信息，请参阅此表后面的提示）。
YYYY-MM-DD HH24:MI:SS.FF

2013-04-28 20:57:01.123456
YYYY-MM-DD"T"HH24:MI:SS

2013-04-28T20:57:01

T 周围的双引号是可选的（有关详细信息，请参阅此表后面的提示）。
YYYY-MM-DD HH24:MI:SS

2013-04-28 20:57:01
YYYY-MM-DD"T"HH24:MI

2013-04-28T20:57

T 周围的双引号是可选的（有关详细信息，请参阅此表后面的提示）。
YYYY-MM-DD HH24:MI

2013-04-28 20:57
YYYY-MM-DD"T"HH24

2013-04-28T20

T 周围的双引号是可选的（有关详细信息，请参阅此表后面的提示）。
YYYY-MM-DD HH24

2013-04-28 20
YYYY-MM-DD"T"HH24:MI:SSTZH:TZM

2013-04-28T20:57:01-07:00

T 周围的双引号是可选的（有关详细信息，请参阅此表后面的提示）。
YYYY-MM-DD HH24:MI:SSTZH:TZM

2013-04-28 20:57:01-07:00
YYYY-MM-DD HH24:MI:SSTZH

2013-04-28 20:57:01-07
YYYY-MM-DD"T"HH24:MITZH:TZM

2013-04-28T20:57+07:00

T 周围的双引号是可选的（有关详细信息，请参阅此表后面的提示）。
YYYY-MM-DD HH24:MITZH:TZM

2013-04-28 20:57+07:00

互联网 (RFC) 时间戳格式
DY, DD MON YYYY HH24:MI:SS TZHTZM

Thu, 21 Dec 2000 16:01:07 +0200
DY, DD MON YYYY HH24:MI:SS.FF TZHTZM

Thu, 21 Dec 2000 16:01:07.123456789 +0200
DY, DD MON YYYY HH12:MI:SS AM TZHTZM

Thu, 21 Dec 2000 04:01:07 PM +0200
DY, DD MON YYYY HH12:MI:SS.FF AM TZHTZM

Thu, 21 Dec 2000 04:01:07.123456789 PM +0200
DY, DD MON YYYY HH24:MI:SS

Thu, 21 Dec 2000 16:01:07
DY, DD MON YYYY HH24:MI:SS.FF

Thu, 21 Dec 2000 16:01:07.123456789
DY, DD MON YYYY HH12:MI:SS AM

Thu, 21 Dec 2000 04:01:07 PM
DY, DD MON YYYY HH12:MI:SS.FF AM

Thu, 21 Dec 2000 04:01:07.123456789 PM

其他时间戳格式
MM/DD/YYYY HH24:MI:SS

2/18/2008 02:36:48

在加载或操作常用欧洲格式的日期（即 DD/MM/YYYY）时，可能会生成不正确的日期。例如，05/02/2013 可能被解释为 2013 年 5 月 2 日，而不是 2013 年 2 月 5 日。
DY MON DD HH24:MI:SS TZHTZM YYYY

Mon Jul 08 18:09:51 +0000 2013

如果时间或时间戳字符串中的数字之后紧跟时区偏移量（例如“0800”），则时区偏移量必须以 + 或 - 开头。当小数秒或时区偏移量未达到允许的最大位数时，该符号可防止歧义。例如，如果小数秒的末位数字和时区的首位数字之间没有分隔符，则时间“04:04:04.321200”中的“1”既可能是小数秒的末位数字（例如 321 毫秒）或时区偏移量的首位数字（例如比 UTC 早 12 小时）。

小技巧

在某些时间戳格式中，字母 T 用作日期和时间之间的分隔符（例如 'YYYY-MM-DD"T"HH24:MI:SS'）。

T 周围的双引号是可选的。但是，Snowflake 建议在 T （和其他字面量）周围使用双引号，以避免歧义。

双引号应仅在格式说明符中使用，而不应 在实际值中使用。例如：

SELECT TO_TIMESTAMP('2019-02-28T23:59:59', 'YYYY-MM-DD"T"HH24:MI:SS');
Copy

此外，T 周围的引号必须是双引号。

有关使用日期、时间和时间戳格式的其他信息

以下各节介绍了日期、时间和时间戳中各个字段的要求和最佳实践。

字段的有效值范围

各个字段的建议值范围如下所示：

字段

值

备注

年

0001 至 9999

在某些上下文中，可能会接受超出此范围的某些值，但 Snowflake 建议仅使用此范围内的值。例如，0000 年是可以接受的，但它并不正确，因为在公历中，公元 1 年紧跟在公元前 1 年之后；并没有 0 年。

月

01 至 12

日

01 至 31

在少于 31 天的月份中，该月的天数即为实际最大值。

时

00 至 23

或者 01–12 （如果您使用的是 HH12 格式）。

分

00 至 59

秒

00 至 59

Snowflake 不支持闰秒；60 和 61 这两个值会被拒绝。

小数

0 至 999999999

小数点后的位数在一定程度上取决于确切的格式说明符（例如，FF3 支持小数点后最多 3 位数字，FF9 支持小数点后最多 9 位数字）。您输入的位数可以比指定位数少（例如，即使您使用 FF9，也允许输入 1 位数字）；不需要末尾零以使字段达到指定宽度。

对格式元素使用正确的位数

对于大多数字段（年、月、日、小时、分钟和秒钟），格式说明符的元素（YYYY、MM、DD 等）是两个或四个字符。

以下规则介绍了实际应在字面量的值中指定多少位数字：

  • YYYY：可以指定年份的 1、2、3 或 4 位数字。但是，Snowflake 建议指定 4 位数字。如有必要，请添加前导零。例如，公元 536 年将是“0536”。

  • YY：指定年份的 1 或 2 位数字。但是，Snowflake 建议指定 2 位数字。如有必要，请添加前导零。

  • MM：指定一位或两位数字。例如，一月可以表示为“01”或“1”。Snowflake 建议使用两位数字。

  • DD：指定一位或两位数字。Snowflake 建议使用两位数字。

  • HH12 和 HH24：指定一位或两位数字。Snowflake 建议使用两位数字。

  • MI：指定一位或两位数字。Snowflake 建议使用两位数字。

  • SS：指定一位或两位数字。Snowflake 建议使用两位数字。

  • FF9：指定 1 至 9 位数字（含）。Snowflake 建议指定实际有效位数。不需要末尾零。

  • TZH：指定一位或两位数字。Snowflake 建议使用两位数字。

  • TZM：指定一位或两位数字。Snowflake 建议使用两位数字。

对于所有字段（小数秒除外），Snowflake 建议指定最大位数。如有必要，请使用前导零。例如，“0001-02-03 04:05:06 -07:00”遵循了建议的格式。

对于小数秒，末尾零是可选的。一般而言，仅指定可靠且有意义的位数被认为是一种良好做法。例如，如果时间测量精确到小数点后 3 位（毫秒），则将其指定为 9 位（例如“.123000000”）可能会具有误导性。

值和格式说明符中的空格

Snowflake 在某些（但不是所有）情况下强制匹配空格。例如，以下语句会生成错误，因为指定值中的日和小时之间没有空格，但格式说明符中的 HH 和 DD 之间却有空格：

SELECT TO_TIMESTAMP('2019-02-2823:59:59 -07:00', 'YYYY-MM-DD HH24:MI:SS TZH:TZM');
Copy

但是，以下语句不会生成错误，即使值中包含空格而说明符中不包含空格：

SELECT TO_TIMESTAMP('2019-02-28 23:59:59.000000000 -07:00', 'YYYY-MM-DDHH24:MI:SS.FF TZH:TZM');
Copy

造成差异的原因是，在前一种情况下，如果字段并非都处于最大宽度，则值将不明确。例如，“213”可以解释为 2 天 13 小时，或 21 天 3 小时。但是，DDHH 与 DD HH 完全相同（空格除外）。

小技巧

尽管为了处理格式可变的数据，允许存在一些空格差异，但 Snowflake 建议值和说明符完全匹配，包括空格。

上下文依赖关系

并非所有限制在所有上下文中都同样会强制执行。例如，有些表达式可能会滚动更新至 2 月 31 日，而有些则不会。

指定格式的最佳实践摘要

这些最佳实践最大限度地减少了 Snowflake 过去、当前和预计未来版本中的歧义和其他潜在问题：

  • 请注意将来自使用不同格式的来源的数据进行混合所存在的危险（例如，将遵循美国常用格式 MM-DD-YYYY 和欧洲常用格式 DD-MM-YYYY 的数据进行混合）。

  • 指定每个字段的最大位数（小数秒除外）。例如，使用 4 位数的年份，必要时指定前导零。

  • 在时间戳中的日期和时间之间指定空格或字母 T。

  • 值和格式说明符中的空格（以及日期和时间之间的可选 T 分隔符）应相同。

  • 如果需要等效的滚动更新，请使用区间算术。

  • 使用 AUTO 格式时应谨慎。如果可能，请指定格式，并确保值始终与指定的格式匹配。

  • 在命令中指定格式比在命令外指定格式更安全，例如在 DATE_INPUT_FORMAT 等参数中。（见下文。）

  • 将脚本从一个环境移动到另一个环境时，请确保与日期相关的参数（如 DATE_INPUT_FORMAT）在新旧环境中相同（假设值的格式也相同）。

日期和时间函数

Snowflake 提供了一组函数来构造、转换、提取或修改 DATE/TIME/TIMESTAMP 数据。有关更多信息，请参阅 日期和时间函数。

对整型存储的日期、时间和时间戳值的 AUTO 检测

对于存储在字符串中的秒或毫秒整数，Snowflake 会尝试根据值的长度确定正确的度量单位。

备注

不建议使用带引号的整数作为输入内容。

例如，要计算自 Unix 时间戳开始以来相当于 1487654321 秒的时间戳，请执行以下语句：

select to_timestamp('1487654321');
-------------------------------+
  TO_TIMESTAMP('1487654321')   |
-------------------------------+
 2017-02-20 21:18:41.000000000 |
-------------------------------+
Copy

以下是自该时间戳开始以来使用毫秒数进行的类似计算：

select to_timestamp('1487654321321');
-------------------------------+
 TO_TIMESTAMP('1487654321321') |
-------------------------------+
 2017-02-20 21:18:41.321000000 |
-------------------------------+
Copy

根据值的大小，Snowflake 使用不同的度量单位：

  • 将字符串转换为整数后，该整数被视为自 Unix 纪元时间 (1970-01-01 00:00:00.000000000 UTC) 开始后的秒数、毫秒数、微秒数或纳秒数。

    • 如果整数小于 31536000000（一年中的毫秒数），则该值被视为秒数。

    • 如果该值大于或等于 31536000000 且小于 31536000000000，则该值被视为毫秒数。

    • 如果该值大于或等于 31536000000000 且小于 31536000000000000，则该值被视为微秒数。

    • 如果该值大于或等于 31536000000000000，则该值被视为纳秒数。

    备注

    弃用警告：未来版本的 Snowflake 可能会自动将字符串化的整数值解释为秒数，而不是毫秒数、微秒数或纳秒数。Snowflake 建议您仅在打算将整数解释为秒数时，才使用包含整数的字符串调用 TO_DATE、TO_TIME 或 TO_TIMESTAMP。

  • 如果评估多行（例如，如果输入内容是包含多行的表的列名），则每个值都将单独得到检查，以确定该值表示的是秒数、毫秒数、微秒数还是纳秒数。

如果格式化的字符串和字符串中的整数被传递给函数，则每个值都将根据字符串的内容得到转换。例如，如果将日期格式的字符串和包含整数的字符串传递给 TO_TIMESTAMP，则该函数会根据每个字符串包含的内容正确解释每个值：

select to_timestamp(column1) from values ('2013-04-05'), ('1487654321');
+-------------------------+
| TO_TIMESTAMP(COLUMN1)   |
|-------------------------|
| 2013-04-05 00:00:00.000 |
| 2017-02-21 05:18:41.000 |
+-------------------------+
Copy

日期和时间函数格式最佳实践

AUTO 检测通常确定正确的输入格式；但是，在某些情况下，它可能无法做出正确的判读。

为避免这种情况，Snowflake 强烈建议遵循以下最佳实践（根据需要用 TO_DATE、DATE 或 TO_TIME、TIME 代替 TO_TIMESTAMP）。

  • 如果有可能产生模棱两可的结果，请避免使用 AUTO 格式。相反，请通过以下方式指定显式格式字符串：

  • 对于包含整数值的字符串，请使用以下语法指定其规模：

    TO_TIMESTAMP(TO_NUMBER(<string_column>), <scale>)
    Copy
语言: 中文