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

日期和时间格式提供表示日期、时间和时间戳的方法。

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

Four-digit [1] year.

YY

Two-digit [1] year, controlled by the TWO_DIGIT_CENTURY_START session parameter. For example, when set to 1980, values of 79 and 80 are parsed as 2079 and 1980, respectively.

MM

Two-digit [1] month (01 = January, and so on).

MON

Abbreviated month name [2].

MMMM

Full month name [2].

DD

Two-digit [1] day of month (01 through 31).

DY

星期的缩写。

HH24

Two digits [1] for hour (00 through 23). You must not specify AM / PM.

HH12

Two digits [1] for hour (01 through 12). You can specify AM / PM.

AM、PM

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

MI

Two digits [1] for minute (00 through 59).

SS

Two digits [1] for second (00 through 59).

FF[0-9]

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

TZH:TZM、TZHTZM、TZH

Two-digit [1] time zone hour and minute, offset from UTC. Can be prefixed by +/- for sign.

UUUU

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

[1] 位数描述了将值序列化为文本时生成的输出。解析文本时，Snowflake 最多可接受的指定位数。例如，天数可以是一位或两位数。

[2] For the MON format element, the output produced when serializing values to text is the abbreviated month name. For the MMMM format element, the output produced when serializing values to text is the full month name. When parsing text, Snowflake accepts the three-digit abbreviation or the full month name for both MON and MMMM. For example, "January" or "Jan", "February" or "Feb", and so on are accepted when parsing text.

备注

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

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

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

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

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');
Copy
+-------------------------------+
| TO_TIMESTAMP('1487654321')    |
|-------------------------------|
| 2017-02-21 05:18:41.000000000 |
+-------------------------------+

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

SELECT TO_TIMESTAMP('1487654321321');
Copy
+-------------------------------+
| TO_TIMESTAMP('1487654321321') |
|-------------------------------|
| 2017-02-21 05:18:41.321000000 |
+-------------------------------+

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

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

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

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

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

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

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

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

SELECT TO_TIMESTAMP(column1) FROM VALUES ('2013-04-05'), ('1487654321');
Copy
+-------------------------+
| TO_TIMESTAMP(COLUMN1)   |
|-------------------------|
| 2013-04-05 00:00:00.000 |
| 2017-02-21 05:18:41.000 |
+-------------------------+

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

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

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

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

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

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