日付と時刻の入出力形式

このトピックの内容:

Snowflakeが使用する入出力形式を決定する方法

日付、時刻、およびタイムスタンプに使用する入出力形式を決定するために、Snowflakeは以下を使用します。

日付、時刻、およびタイムスタンプのセッションパラメーター

一連のセッションパラメーターは、日付、時刻、タイムスタンプデータがSnowflakeに出入りする方法、およびタイムゾーンをサポートするタイムおよびタイムスタンプ形式で使用されるタイムゾーンを決定します。

パラメーターは、アカウント、ユーザー、およびセッションレベルで設定できます。 SHOW PARAMETERS コマンドを実行して、現在のセッションのすべての操作に適用される現在のパラメーター設定を表示します。

入力形式

以下のパラメーターは、 COPY、 INSERT、および MERGE 操作を含む、 DMLで認識される日付、時刻、およびタイムスタンプの形式を定義します。

3つのパラメーターすべてのデフォルトは AUTO です。パラメーター値を AUTO に設定すると、Snowflakeは、 AUTO 検出でサポートされている形式 にリストされている形式のいずれかを使用して、入力式の日付、時刻、またはタイムスタンプ文字列の照合を試みます。

  • 一致する形式が見つかった場合、Snowflakeは文字列を受け入れます。

  • 一致する形式が見つからない場合、Snowflakeはエラーを返します。

出力形式

次のパラメーターは、Snowflakeからの日付および時刻出力の形式を定義します。

さらに、次のパラメーターは TIMESTAMP データ型エイリアスを3つの TIMESTAMP_* バリエーションのいずれかにマップします。

タイムゾーン

次のパラメーターはタイムゾーンを決定します。

日付、時刻、およびタイムスタンプのロード/アンロードのファイル形式オプション

Snowflakeは、入力および出力形式パラメーターとは別に、Snowflakeテーブルにデータをロードまたはアンロードするときに使用する、3つのファイル形式オプションを提供します。

  • DATE_FORMAT

  • TIME_FORMAT

  • TIMESTAMP_FORMAT

オプションは、 COPY コマンドで直接指定するか、 COPY コマンドで参照される名前付きステージまたはファイル形式オブジェクトで指定できます。指定するとこれらのオプションは、対応する入力形式（データのロード時）または出力形式（データのアンロード時）を上書きします。

データのロード

データのロードで使用する場合、オプションはステージングされたデータファイルの日付、時刻、およびタイムスタンプの文字列の形式を指定します。オプションは、 DATE_INPUT_FORMAT、 TIME_INPUT_FORMAT、または TIMESTAMP_INPUT_FORMAT パラメーター設定を上書きします。

これらすべてのオプションのデフォルトは AUTOです。つまり、 COPY INTO <テーブル> コマンドは、 AUTO 検出でサポートされている形式 にリストされている形式のいずれかで、ステージングされたデータファイル内にあるすべての日付とタイムスタンプ文字列の照合を試みます。

  • 一致する形式が見つかった場合、Snowflakeは文字列を受け入れます。

  • 一致する形式が見つからない場合、Snowflakeはエラーを返し、 ON_ERROR コピーオプションに指定されたアクションを実行します。

警告

Snowflakeは、最も一般的な日付、時刻、およびタイムスタンプ形式の自動検出をサポートしています（以下のテーブルを参照）。ただし、一部の形式ではあいまいな結果が生成される場合があり、データのロードに AUTO を使用するとSnowflakeが誤った形式を適用する可能性があります。

データの正しいロードを保証するため、データロードのファイル形式オプションを明示的に設定することを、Snowflakeは 強く お勧めします。

データのアンロード

データのアンロードで使用する場合、オプションは、指定されたステージのファイルにアンロードされた日付、時刻、およびタイムスタンプに適用される形式を指定します。

これらすべてのオプションのデフォルトは AUTO です。つまり、Snowflakeは次のパラメーターで指定されたフォーマットを適用します。

  • DATE_OUTPUT_FORMAT

  • TIME_OUTPUT_FORMAT

  • TIMESTAMP_*_OUTPUT_FORMAT （ TIMESTAMP_TYPE_MAPPING の設定による）

入出力形式で使用される要素について

パラメーター、 ファイル形式オプション、 変換関数 で指定する入出力形式では、次のテーブルに示す要素を使用できます。

次のセクション でも、これらの要素を使用して、Snowflakeにより自動で認識される形式について説明します。

形式要素

説明

YYYY

年を表す4桁の数字。

YY

TWO_DIGIT_CENTURY_START セッションパラメーターによって制御される年を表す2桁の数字。例： 1980 に設定されている場合、 79 および 80 の値はそれぞれ 2079 および 1980 として解析されます。

MM

月を表す2桁の数字（01=1月など）。

MON

完全な月名または短縮された月名。

MMMM

完全な月名。

DD

日を表す2桁の数字（01から31）。

DY

曜日の略称。

HH24

時間を表す2桁の数字（00から23）。 AM / PM は指定 できません。

HH12

時間を表す2桁の数字（01から12）。 AM / PM を指定できます。

AM 、 PM

午前（am）/午後（pm）。これは HH12 でのみ使用します （HH24 では使用 できません）。

MI

分を表す2桁の数字（00から59）。

SS

秒を表す2桁の数字（00から59）。

FF[0-9]

精度0（秒）から9（ナノ秒）までの小数秒。例： FF、 FF0、 FF3、 FF9。 FF を指定することは、 FF9 （ナノ秒）と同等です。

TZH:TZM 、 TZHTZM 、 TZH

タイムゾーンの時間と分、 UTC との時差。記号の前に +/- を付けることができます。

注釈

  • 日付のみの形式を使用する場合、関連付けられた時刻はその日の午前0時と見なされます。

  • 二重引用符で囲まれた形式または上記の要素以外の形式のものは、解釈されることなく解析/フォーマットされます。

  • 有効な範囲、桁数、ベストプラクティスの詳細については、 日付、時刻、およびタイムスタンプ形式の使用に関する追加情報 をご参照ください。

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年2月5日ではなく、2013年5月2日と解釈される場合があります。

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年2月5日ではなく、2013年5月2日と解釈される場合があります。
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

または、 HH12 形式を使用している場合は 01 ～ 12 。

分

00 ～ 59

秒

00 ～ 59

Snowflakeは、うるう秒またはダブルうるう秒をサポートしていません。値 60 および 61 は拒否されます。

分数

0 ～ 999999999

小数点以下の桁数は、正確な書式指定子に一部依存します（たとえば、 FF3 は小数点以下3桁までサポートし、 FF9 は小数点以下9桁までサポート）。指定した数字よりも少ない数字を入力できます（例： FF9 を使用する場合でも1桁を許可）。フィールドを指定した幅まで埋めるための、後続のゼロは必要ありません。

書式要素で正しい桁数を使用する

ほとんどのフィールド（年、月、日、時間、分、秒）の場合、書式指定子の要素（YYYY、 MM、 DD など）は2または4文字です。

リテラル値で実際に指定する必要がある桁数のルールが次に示されています。

  • YYYY: 年に、1桁、2桁、3桁、または4桁を指定できます。ただし、Snowflakeは4桁を指定することをお勧めします。必要に応じて、先頭にゼロを追加します。例えば、西暦536年は「0536」になります。

  • YY: 年に、1桁または2桁を指定します。ただし、Snowflakeは2桁を指定することをお勧めします。必要に応じて、先頭にゼロを追加します。

  • MM: 1桁または2桁を指定します。例えば、1月は「01」または「1」として表すことができます。Snowflakeでは、2桁の数字を使用することをお勧めします。

  • DD: 1桁または2桁を指定します。Snowflakeでは、2桁の数字を使用することをお勧めします。

  • HH12 および HH24: 1桁または2桁を指定します。Snowflakeでは、2桁の数字を使用することをお勧めします。

  • MI: 1桁または2桁を指定します。Snowflakeでは、2桁の数字を使用することをお勧めします。

  • SS: 1桁または2桁を指定します。Snowflakeでは、2桁の数字を使用することをお勧めします。

  • FF9: 1から9桁（この値を含む）の間で指定します。Snowflakeは、実際の有意な桁数を指定することをお勧めします。後続のゼロは必要ありません。

  • TZH: 1桁または2桁を指定します。Snowflakeでは、2桁の数字を使用することをお勧めします。

  • TZM: 1桁または2桁を指定します。Snowflakeでは、2桁の数字を使用することをお勧めします。

すべてのフィールド（秒の小数部を除く）について、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');
-------------------------------+
  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（1年のミリ秒数）より小さい場合、値は秒数として扱われます。

    • 値が31536000000以上で31536000000000未満の場合、値はミリ秒として扱われます。

    • 値が31536000000000以上で31536000000000000より小さい場合、値はマイクロ秒として扱われます。

    • 値が31536000000000000以上の場合、値はナノ秒として扱われます。

    ご用心

    現在、負の値は常に秒として扱われます。例えば、-31536000000000000000は1970年の前の秒数として扱われますが、そのスケールは、ナノ秒として使用することを意図していることを示しています。

    この動作は将来変更される可能性があります。

    注釈

    非推奨の警告: 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_TIMESTAMP を TO_DATE , DATE または TO_TIME , TIME に置き換える）。

  • 結果があいまいになる可能性がある場合は、 AUTO 形式の使用を避けてください。その代わりに、次の方法で明示的なフォーマット文字列を指定します。

  • 整数値を含む文字列の場合、次の構文を使用してスケールを指定します。

    TO_TIMESTAMP(TO_NUMBER(<string_column>), <scale>)
    Copy
言語: 日本語