目的
フィールドを日付値として識別し、日付変数の処理と形式設定を単純化します。
構文
- attributes
- 適切なデータ型、列記憶、または列制約属性。
ANSI準拠
ANSIDateのDateFormによるDATEは、ANSI SQL:2011に準拠しています。
IntegerDateのDateFormによるDATEは、ANSI SQL:2011規格に対するTeradataの拡張機能です。
日付の内部表現
Teradata Databaseは、内部的には以下の公式に基づいて各日付値を符号付き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 | 结果 |
|---|---|
| ANSIDate | エクスポート データ型は、YYYY-MM-DDの形式のクライアント文字セットのCHARACTER(10)になります。 |
| IntegerDate | エクスポート データ型は次のようになります。
|
アプリケーション定義とクライアント データ型を判別するのは、アプリケーション プログラマの責任です。
DateFormオプションを設定する操作の概要については、DATE形式の階層を参照してください。
DATE形式
日付の処理をシンプルにするために、Teradata Databaseでは、事前設定形式で日付を扱うことを前提としています。データベース管理者は、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);
文字列日付の妥当性検査
文字列を日付値に変換する場合、Teradata Databaseは、文字列の構成要素の妥当性検査を実施します。その内容を以下のテーブルにまとめておきます。
| コンポーネント | 要件 | 例 |
|---|---|---|
| 日付文字列 - LATIN文字セット | 単一引用符で囲みます。 | 'Jan 28, 1960' |
| 日付文字列 - Unicode 16進形式 | 単一引用符で囲み、末尾にxcを付けます。 | ' 79797979C7 AF6D6DB7EE 6464C6FC 'xc |
| 区切り文字 | 数字以外の任意の文字を区切り文字として使用できます。日付内部の区切り文字をすべて同じものにする必要はありません。 | / |
| YY | カレンダ用として有効な2桁または4桁の数値文字として表記された年。世紀の変わり目の移行をサポートするため、Teradata Databaseは、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によって指定されている名前のいずれかに一致する曜日名。 | Friday |
CHARACTERからDATEへの変換の詳細は、<Teradata Vantage™ SQL関数、式、および述部、B035-1145>の「データ型の変換」を参照してください。
数値日付の妥当性検査
Teradata Databaseの格納形式の数値で日付を入力することもできます。ただし、この方法はお勧めできません。Teradata Databaseは、以下の公式に基づいて、各日付値を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に設定されている場合、Teradata Databaseは日付をこの数値形式でエクスポートします。
以下のテーブルは、数値日付が列に挿入された場合にどのように解釈されるかを示したものです。特に、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の暗黙の変換と明示的な変換
割り当てや比較などの特定の操作の場合、Teradata DatabaseはDATEタイプに対して暗黙の変換を実行します。これには、DATEタイプからTIMESTAMPタイプへの暗黙の変換が含まれる場合もあります。CASTを使用することにより、DATEタイプをTIMESTAMPタイプまたは他のタイプに、明示的に変換することもできます。
DATEの暗黙の変換が実行されるすべての事例の詳細については、<Teradata Vantage™ SQL関数、式、および述部、B035-1145>の「データ型の変換」を参照してください。
DATEの算術演算
DATEとして宣言されたフィールドは、加算および減算によって操作することができます。MIN、MAX、AVERAGE、およびCOUNT集約演算子も、日付値と共に使用することができます。
日付データに対しては、算術演算を実行することができます。例えば、DATEフィールドやDATE組込み値との間で日数を加算または減算することができます。そのような演算の結果も、DATEデータ型の値になります。
2つの日付の間の日数は、一方の日付値を他方の日付値から減算することによって算出することができます。結果は整数になります。
Teradata Databaseは、月や年をまたいだ計算や、うるう年の計算を自動的に処理します。また、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システム ビュー
Teradata Databaseは一揃いのカレンダ関数と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>を参照してください。
例: 日付を使用するクエリー
現在50歳を超えるすべての男性従業員をリストするためには、以下の文を入力することができます。
SELECT name, dob FROM employee WHERE CURRENT_DATE > ADD_MONTHS (dob, 12*50) AND sex = 'M' ;
システムは、以下のような結果を返します。
Name DOB --------- ---------- Russell S 1932-06-05 Carter J 1935-03-12 Inglis C 1938-03-07
システムから現在の日付を取得するために、式の中でCURRENT_DATEが使用されていることに注意する必要があります。
従業員Russellの生年月日から3ヶ月を投影するためには、以下のように入力します。
SELECT name, ADD_MONTHS (dob,3) FROM employee WHERE name = ’Russell S’ ;
システムは、以下のような結果を返します。
Name ADD_MONTHS(dob,3) --------- ----------------- Russell S 1932-09-05
例: 日付を表わすための整数の使用
1938年3月7日から1942年8月25日の間に生まれた従業員をリストするためには、日付情報を以下のように指定することができます。
SELECT name, dob FROM employee WHERE dob BETWEEN 380307 AND DATE '1942-08-25' ORDER BY dob ;
この例で、1つ目の日付(380307)はyymmddを表わす整数です。dob列の値は、整数の値と比較するためにINTEGERに変換されます。2番目の日付は、優先使用の日付リテラル形式になっています。結果として、Employeeテーブルに指定された生年月日の情報が以下のように返されます。
Name DOB ---------- ----------- Inglis C Mar 07 1938 Peterson J Mar 27 1942
DOBの表示は、Personnel.Employee.DOBの形式によって設定されます。 FORMAT ' MMM DD YYYY '。
例: 日付形式の変更
上記の例で表示されたDATE形式を別の形式に変えるためには、SELECTを以下のように変更します。
SELECT name, dob (FORMAT '99-99-99') FROM employee WHERE dob BETWEEN 380307 AND DATE '1942-08-25' ORDER BY dob ;
この形式指定によって、表示が以下のように変わります。
Name DOB ---------- -------- Inglis C 38-03-07 Peterson J 42-03-27
例: 日付形式から整数形式への変更
表示をDATE形式から整数形式に変えるためには、例3の文を以下のように変更します。
SELECT name, dob (INTEGER) FROM employee WHERE dob BETWEEN 380307 AND 420825 ORDER BY dob ;
この形式指定によって、表示が以下のように変わります。
Name DOB ---------- ----------- Inglis C 380307 Peterson J 420327
DATEに対する算術演算を示す他の例を、以下のテーブルに示します。システム日付は2001年1月24日とします。
| 文 | システムから返される値 |
|---|---|
| SELECT CURRENT_DATE; | Date -------- 01/01/24 |
| SELECT CURRENT_DATE +3; | (Date+3)--------01/01/27 |
| SELECT CURRENT_DATE -3; | (Date-3)--------01/01/21 |
| SELECT CURRENT_DATE - CURRENT_DATE; | (Date-Date)----------- 0 |