サーバ側ロジック（ObjectScript）で SQL を実行するときの DATE 型や TIME 型カラムの操作方法

記事

Mihoko Iijima · 2020年10月15日 5m read

#ObjectScript #SQL #Caché #InterSystems IRIS #InterSystems IRIS for Health

これはInterSystems FAQ サイトの記事です。

DATE 型は InterSystems 製品のデータ型の %Date に、TIME 型は %Time に対応しています。

%Date は内部日付（特殊変数 $Horolog のカンマ区切り1番目）、%Time は内部時刻（$Horolog のカンマ区切り2番目）を登録するタイプであるため、サーバ側ロジックでは表示モードを切り替えない限り、内部（論理）形式の値が使用されます。
サーバ側ロジックで内部日付・時刻の表示形式を変更する方法は、操作方法により異なります。

以降の実行例では、Sample.Person テーブルを使用して解説します。
（コマンド実行例は SELECT 文で記載していますが、更新文に対しても同様に記述できます。）

IRIS／IRIS for Health でお試しいただく場合は、ドキュメント（InterSystems IRIS で使用するサンプルのダウンロード）から、
または関連記事（サンプル（Sample.Person）のクラス定義ダウンロードとサンプルデータの作成について）から、
Sample.Person クラスのインポートとサンプルデータの作成を行ってからお試しください。

Caché／Ensembleでお試しいただく場合は、SAMPLESネームスペースのSample.Personをご利用ください。

(1) 埋め込みSQLを利用する場合

埋め込みSQLを利用して表示形式を切り替えるには、#sqlcomple select を使用します。
指定できる値は以下の通りです。

Logical（デフォルト）
Display
ODBC
Runtime

ドキュメント（IRIS）：埋め込み SQL のコンパイルとマクロ・プリプロセッサ【IRIS】
ドキュメント：埋め込み SQL のコンパイルとマクロ・プリプロセッサ

#sqlcompile select=ODBC
&sql(declare C1 Cursor for select ID,Name,DOB into :pid,:name,:dob from Sample.Person where ID<=5)
&sql(open C1)
for {
   &sql(fetch C1)
   if SQLCODE'=0 { quit }
　  //表示例）1-Mastrolito,Susan T.-2013-01-01
   write pid,"-",name,"-",dob,!
}
&sql(close C1)

(2) ダイナミックSQLを利用する場合

%SQL.Statement を利用したダイナミックSQLで表示形式を切り替えるには、%SelectModeプロパティを利用します。
※このプロパティは%Execute()の実行前に設定している必要があります。

設定できる値は以下の通りです。

0 ：論理モード
1 ：ODBCモード
2 ：表示モード

SAMPLES>set sql="select ID,Name,DOB from Sample.Person where ID <= 5" SAMPLES>set stmt=##class(%SQL.Statement).%New() SAMPLES>set st=stmt.%Prepare(sql) SAMPLES>set rset=stmt.%Execute() SAMPLES>do rset.%Display()
ID Name DOB
1 Gallant,Yan N. 42146
2 Waal,Umberto G. 45359
3 Jenkins,Sam A. 37404
4 Marks,Milhouse B. 52043
5 Hernandez,Phyllis W. 64590 5 Rows(s) Affected
SAMPLES>

(3) クラスクエリを使用する場合

クラスクエリで表示形式を切り替えるには、Query定義のパラメータ：SELECTMODE を利用します。
指定できる値は以下の通りです。

RUNTIME（デフォルト）
LOGICAL
DISPLAY
ODBC

定義例は以下の通りです。

Query NewQuery1() As %SQLQuery(SELECTMODE = "ODBC")
{
select ID,Name,DOB from Sample.Person where ID<=5
}

(4) 実行中プロセスに対して表示形式を切り替える方法

システムオブジェクト $SYSTEM.SQL.SetSelectMode() を利用して、カレントプロセスの表示形式を変更できます。
引数、戻り値は以下の通りです。

第1引数に　0（Logical）、1（ODBC）、2（Display）を指定します。
第2引数は、実行結果が%Statusで設定される参照渡しの引数です。
戻り値には、現在の表示モードの番号が返ります。

詳細は下記ドキュメントページをご参照ください。

InterSystems SQL の基礎 - データ表示オプション【IRIS】
CachéSQLの基礎_データ表示オプション

// デフォルトからODBC形式に変更
SAMPLES>set cm=$system.SQL.SetSelectMode(1,.st)

SAMPLES>set sql="select ID,Name,DOB from Sample.Person where ID <= 5"

SAMPLES>set stmt=##class(%SQL.Statement).%New()

SAMPLES>set st=stmt.%Prepare(sql)

SAMPLES>set rset=stmt.%Execute()

SAMPLES>do rset.%Display()
ID Name DOB
1 Gallant,Yan N. 1956-05-23
2 Waal,Umberto G. 1965-03-10
3 Jenkins,Sam A. 1943-05-30
4 Marks,Milhouse B. 1983-06-28
5 Hernandez,Phyllis W. 2017-11-03 5 Rows(s) Affected
SAMPLES>

※プロセスに対して表示形式を切り替えた後、各SQL実行方法で表示形式を変更した場合は、最後に指定した表示形式が採用されます。

(5) ObjectScript 関数を使用して、表示形式を変換する方法

ObjectScript の表示変換用関数を利用して、内部形式⇔表示形式の変換を行う方法もあります。

日付用関数では
　表示→内部形式　$ZDATEH(yyyymmdd,8)　または、$ZDATE(yyyy-mm-dd,3)
　内部→表示形式　YYYYMMDDの結果を取得する場合：$ZDATE(+$Horolog,8)
YYYY-MM-DDの結果を取得する場合：$ZDATEH(+$H,3) 時間用関数では
　表示→内部形式　$ZTIMEH("HH:MM:SS")
　内部→表示形式　HH:MM:SS の結果を取得する場合：$ZTIMEH($piece($Horolog,",",2)) 日付時刻を取り扱う　$ZDATETIME()と$ZDATETIMEH()関数もあります。

日付用関数の詳細は下記ドキュメントページをご参照下さい。
ObjectScript関数【IRIS】
ObjectScript関数

SAMPLES>write $horolog
63895,34979
SAMPLES>write $ZDATE(+$horolog,8) // yyyymmddの形式へ変換
20151209
SAMPLES>write $ZDATE(+$horolog,3) // yyyy-mm-ddの形式へ変換
2015-12-09
SAMPLES>write $ZDATEH(20151209,8) // yyyymmddから内部形式へ変換
63895
SAMPLES>write $ZDATEH("2015-12-09",3) // yyyy-mm-ddから内部形式へ変換
63895
SAMPLES>write $ZTIME($piece($horolog,",",2)) // 内部形式から時刻への変換
09:44:16
SAMPLES>write $ZTIMEH("10:01:11") // 表示時刻から内部形式への変換
36071
SAMPLES>write $ZDATETIME($horolog,8) // $horologを利用した日付時刻の変換
20151209 09:45:15
SAMPLES>write $ZDATETIME($horolog,3)
2015-12-09 09:45:16
SAMPLES>