日付の内部表現
SQL Engineは、内部的には以下の公式に基づいて各日付値を符号付き4バイト整数で格納します。
(YEAR - 1900) * 10000 + (MONTH * 100) + DAY
YEAR、MONTH、およびDAYは、それぞれグレゴリオ暦の年、月、日であり、その値の範囲は以下のとおりです。
| コンポーネント | 値の範囲 | |||
|---|---|---|---|---|
| 最小値 | 最大値 | |||
| YEAR | 1 | 9999 | ||
| MONTH | 1 | 12 | ||
| DAY | 1 | 28、29、30、または31 (月と年に対応) |
||
日付の外部表現
クライアントの外部DATE形式は、DateFormオプションがIntegerDateモードに設定されているか、ANSIDateモードに設定されているかによって異なります。
| DateForm | THEN … |
|---|---|
| ANSIDate | エクスポート データ型は、YYYY-MM-DDの形式のクライアント文字セットのCHARACTER(10)になります。 |
| IntegerDate | エクスポート データ型は次のようになります。
|
アプリケーション定義とクライアント データ型を判別するのは、アプリケーション プログラマの責任です。
DateFormオプションを設定する操作の概要については、DATE形式の階層を参照してください。
DATE形式
日付の処理をシンプルにするために、Vantageでは、事前設定形式で日付を扱うことを前提としています。データベース管理者は、DateFormの設定、およびデータ形式仕様(SDF)ファイルのDATE形式のデフォルトの設定を使って、システム用の形式を設定します。
- 日付値のエクスポート データ型
- 日付値のデータ入力形式
- 実行するマクロ内の日付値の表示形式
- 文字列と日付の比較や変換のためのDATE形式
- 新規作成のテーブルやビューのDATE列の表示形式
- システム レベルでDateFormを変更すること
- ユーザー レベルまたはセッション レベルでDateFormを設定すること
- SDFのシステム デフォルトDATE形式を変更すること
- FORMAT句で形式を指定すること
DATE形式およびそれらを変更する方法の詳細については、データ型のデフォルトの形式およびDATE形式を参照してください。
日付の入力
- 文字列
- 数値
- ANSI日付リテラル
優先的に使用するのは、ANSI日付リテラルとして日付を指定する方法です。有効なANSI日付リテラルは、SQLの日付処理用のその他の形式検査を必要としません。例えば、以下のSQL文は、参照先の列のDATE形式がどんなものであっても、正しく動作します。
INSERT INTO DATETAB VALUES (DATE '2001-12-20');
日付を単に文字列として指定する場合は、参照先の列のDATE形式と一致させる必要があります。SQLでは、両者の日付文字列が同じ形式になっていることが必要です。例えば、DOB列のDATE形式が'YYYY-MM-DD'の場合、以下の文はエラーになります。
SELECT Name FROM Employee WHERE DOB = 'Jan 31 1948';
日付の有効な形式を構成する形式設定文字については、日付情報の形式設定文字を参照してください。
日付は、数値として指定することもできます。日付を数値として指定する場合は、形式のチェックは不要ですが、1900年代以外の年号は明確さに欠けて、エラーを起こしやすくなります。後続の節で数値日付の妥当性検査について参照してください。
以下は、2001-12-20を数値として入力する場合の例です。
INSERT INTO DATETAB VALUES (1011220);
文字列日付の妥当性検査
文字列を日付値に変換する場合、Vantageは、文字列の構成要素の妥当性検査を実施します。その内容を以下のテーブルにまとめておきます。
| コンポーネント | 要件 | 例 |
|---|---|---|
| 日付文字列 - LATIN文字セット | 単一引用符で囲みます。 | 'Jan 28, 1960' |
| 日付文字列 - Unicode 16進形式 | 単一引用符で囲み、末尾にxcを付けます。 | ' 79797979C7 AF6D6DB7EE 6464C6FC 'xc |
| 区切り文字 | 数字以外の任意の文字を区切り文字として使用できます。日付内部の区切り文字をすべて同じものにする必要はありません。 | / |
| YY | カレンダ用として有効な2桁または4桁の数値文字として表記された年。世紀の変わり目の移行をサポートするため、Vantageは、2桁の年号形式においても4桁の年号を受け入れます。 2桁の年号は、Century Break機能によって世紀が判別されます。詳細については、<Teradata Vantage™ - データベース ユーティリティ、B035-1102>を参照してください。
|
05 2003 |
| YYYY Y4 |
カレンダ用として有効な4桁の数値文字で表記された年。 | 2003 |
| MM | カレンダ用として有効な2桁の数値文字で表記された月。 | 02 |
| MMM M3 |
現在のデータ形式仕様(SDF)ファイルの中でShortMonthsで指定されている名前のいずれかに一致する月略称。 | Jan |
| MMMM M4 |
現在のSDFの中でLongMonthsによって指定されている名前のいずれかに一致する月正式名。 | January |
| DD | カレンダ用として有効な2桁の数値文字で表記された日。 | 03 |
| DDD D3 |
カレンダ用として有効な3桁の数値文字で表記された日。日付に月が含まれている場合、その日はその月に存在していることが必要です。 | 056 |
| EEE E3 |
現在のSDFの中でShortDaysによって指定されている名前のいずれかに一致する曜日略称。 | Fri |
| EEEE E4 |
現在のSDFの中でLongDaysによって指定されている名前のいずれかに一致する曜日名。 | 金曜日 |
CHARACTERからDATEへの変換の詳細については、<Teradata Vantage™ - SQL関数、式、および述部、B035-1145>を参照してください。
数値日付の妥当性検査
データベースの格納形式の数値で日付を入力することもできます。ただし、この方法はお勧めできません。SQL Engineは、以下の公式に基づいて、各日付値を4バイト整数で格納します。
(year - 1900) * 10000 + (month * 100) + day
ここで許容されている日付値の範囲は、西暦0001年1月1日から西暦9999年12月31日までです。例えば、1985年12月31日は851231という整数として、1776年7月4日は-1239296として、2041年3月30日は1410330として、それぞれ格納されます。
Century Break機能は、数値日付に影響を及ぼしません。
DateFormの現在の設定がIntegerDateに設定されている場合、Vantageは日付をこの数値形式でエクスポートします。
以下のテーブルは、数値日付が列に挿入された場合にどのように解釈されるかを示したものです。特に、3番目の日付の変換に注目してください。これは、1990年12月01日を想定した日付と思われます。
| 生データの値 | 変換後の値 |
|---|---|
| 901201 | 1990-12-01 |
| 1001201 | 2000-12-01 |
| 19901201 | 3890-12-01 |
この式は1900年代の2桁形式の日付に最も適していることに注意してください。1900年代以外でこの形式を使用するのは難しいので、代わりにANSI日付リテラルとして日付を指定することを推奨します。
NUMERICからDATEへの変換の詳細については、<Teradata Vantage™ - SQL関数、式、および述部、B035-1145>を参照してください。
DATEの暗黙の変換と明示的な変換
割り当てや比較などの特定の操作の場合、VantageはDATEタイプに対して暗黙の変換を実行します。これには、DATEタイプからTIMESTAMPタイプへの暗黙の変換が含まれる場合もあります。CASTを使用することにより、DATEタイプをTIMESTAMPタイプまたは他のタイプに、明示的に変換することもできます。
DATEの暗黙の変換が実行されるすべての事例の詳細については、<Teradata Vantage™ - SQL関数、式、および述部、B035-1145>を参照してください。
DATEの算術演算
DATEとして宣言されたフィールドは、加算および減算によって操作することができます。MIN、MAX、AVERAGE、およびCOUNT集約演算子も、日付値と共に使用することができます。
日付データに対しては、算術演算を実行することができます。例えば、DATEフィールドやDATE組込み値との間で日数を加算または減算することができます。そのような演算の結果も、DATEデータ型の値になります。
2つの日付の間の日数は、一方の日付値を他方の日付値から減算することによって算出することができます。結果は整数になります。
Vantageは、月や年をまたいだ計算や、うるう年の計算を自動的に処理します。また、1998-02-30のような無効な日付を格納することもありません。
以下の列の定義では、DOBにDATEデータ型が割り当てられています。
DOB DATE FORMAT 'YYYY-MM-DD' NOT NULL
- 算術(加算および減算)
SMALLINTやINTEGERなどの数値タイプの加算および減算も実行できますが、DATEはANSIでは数値データ型ではないため、これらは非ANSI演算になります。
そのような算術演算は、数値のタイプがINTERVAL DAYになっている場合と同様に処理されます。
例えば、DATE - 12はANSIの式DATE - INTERVAL ‘12’ DAYと同等です。
- 比較
- =
- <>
- >
- ≥
- <
- ≤
- OVERLAPS
- 形式変換
- ADD_MONTHS関数
- EXTRACT関数
DATEタイプに対する算術演算の詳細については、<Teradata Vantage™ - SQL関数、式、および述部、B035-1145>を参照してください。
カレンダ関数およびCALENDARシステム ビュー
Vantageは一揃いのカレンダ関数とSYS_CALENDAR.CALENDARビューを提供して、カレンダ属性を使用する日付/時刻操作をサポートしています。例えば、td_day_of_month関数は、月初めから指定された日までの日数を返します。カレンダ関数は、同様の結果を得るためにCALENDARビューを使用する方法に比べて、パフォーマンスを向上させます。
カレンダ関数についての詳細は、<Teradata Vantage™ - SQL関数、式、および述部、B035-1145>を参照してください。
CALENDARビューを使用すると、1900年から2100年の間の任意の日付属性(例えば、ある日付が何曜日か、あるいは月の第何週かなど)を取得することができます。
CALENDARビュー上でビューを定義すると便利です。CALENDAR上で定義すると便利なビューは、TODAYです。これは、以下のように定義されます。
CREATE VIEW Today AS ( SELECT * FROM SYS_CALENDAR.Calendar WHERE SYS_CALENDAR.Calendar.calendar_date = DATE );
CALENDARでは、算術式と集約を簡単に指定することができます。これは、週、月、年月日、年等ごとに集約された値のリクエストが通常に行なわれるOLAP環境では特に有用です。
SYS_CALENDAR.CALENDARシステム ビューの定義の詳細については、<Teradata Vantage™ - データ ディクショナリ、B035-1092>を参照してください。