TIMESTAMP型の項目に対して、TO_CHAR() や TO_DATE() を用いた SELECT を実行するとエラーになります |

記事

Megumi Kakechi · 2023年4月11日 4m read

#SQL #ヒントとコツ #InterSystems IRIS #InterSystems IRIS for Health

Question：

TIMESTAMP型の項目に対して、TO_CHAR() や TO_DATE() を用いた SELECT を実行すると以下のエラーになります。

実行SQL：

select 
  TO_CHAR(xxxDateTime,'YYYY-MM-DD')
from
  TestObjectScript
ObjectScript

エラー：
[SQLCODE: <-400>:<深刻なエラーが発生しました>]
[%msg: <Unexpected error occurred: <ZCHAR>IllegalValuePassedToTOCHAR^%qarfunc>]

エラーの原因を教えてください。

Answer：

こちらは、IRIS2022.1以降のバージョンで CREATE TABLE (DDL) の TIMESTAMP 型が IRIS側クラスで %Library.PosixTime にマッピングするように変更されているためです。
(アップグレードした環境の場合は、従来のままの %Library.TimeStamp にマッピングされています)

%TimeStamp は、データを人が読める文字列（yyyy-mm-dd hh:mm:ss.ffff）として保存します。
対して、%PosixTime は64bitの整数で保持するため、ディスクやメモリ上のデータサイズを削減し、比較演算処理等のパフォーマンスが向上します。
SQLクエリを高速化したい場合は、%PosixTime を使用されることをお勧めします。

TIMESTAMP型を、従来と同じ %TimeStamp にマッピングするDDLデータ型としては TIMESTAMP2 が用意されています。
CREATE TABLE文で、TIMESTAMP型にしたいフィールドの箇所を TIMESTAMP2 とすることで %TimeStamp とすることができます。

例：

create table Test2 (xxxDateTime TIMESTAMP, xxxDateTime2 TIMESTAMP2)ObjectScript
ObjectScript

※ xxxDateTime は %PosixTime 、xxxDateTime2 は %TimeStamp となる。

全体の設定変更で対応する場合は、
　管理ポータル：
　　　[システム管理] > [構成] > [SQLとオブジェクトの設定] > [システムDDLマッピング]
より TIMESTAMP 行の「編集」をクリックしてデータタイプを %Library.PosixTime から %Library.TimeStamp に変更します。

　↓

この設定変更後に実行される CREATE TABLEより、この変更が反映されるようになります。

管理ポータルで現在のテーブルのフィールドがどちらのデータタイプになっているかを確認できます。

管理ポータル：
　[システムエクスプローラ] > [SQL]

Tips：

%PosixTime のフィールドから YYYY-MM-DD の日付部分のみを取得したい場合は、次のように一旦TIMESTAMPにCAST()してから TO_CHAR() を使用します。

select 
  xxxDateTime,
  to_char(cast(xxxDateTime as timestamp),'YYYY-MM-DD') 
from
  TestObjectScript
ObjectScript

%PosixTime のデータの Insert は %TimeStamp と同じように行えます。

USER>do $SYSTEM.SQL.Shell()
SQL Command Line Shell
----------------------------------------------------
 
The command prefix is currently set to: <<nothing>>.
Enter <command>, 'q' to quit, '?' for help.
[SQL]USER>>set selectmode=odbc      // ODBCモードでテスト                                                 
selectmode = odbc
[SQL]USER>>insert into Test2 (xxxDateTime, xxxDateTime2) values ('2023-02-20 13:45:00','2023-02-20 13:45:00')
6.      insert into Test2 (xxxDateTime, xxxDateTime2) values ('2023-02-20 13:45:00','2023-02-20 13:45:00')
 
1 Row Affected
statement prepare time(s)/globals/cmds/disk: 0.0010s/32/4,002/0ms
          execute time(s)/globals/cmds/disk: 0.0002s/3/183/0ms
                          cached query class: %sqlcq.XXX.cls12
---------------------------------------------------------------------------
[SQL]USER>>select * from Test2     // そのままSelect(※ xxxDateTime :%PosixTime , xxxDateTime2 :%TimeStamp )
7.      select * from Test2
 
xxxDateTime     xxxDateTime2
2023-02-20 13:45:00     2023-02-20 13:45:00
1 Rows(s) Affected
statement prepare time(s)/globals/cmds/disk: 0.0004s/26/978/0ms
          execute time(s)/globals/cmds/disk: 0.0001s/2/528/0ms
                          cached query class: %sqlcq.XXX.cls13
---------------------------------------------------------------------------
[SQL]USER>>q
USER>zw ^poCN.Dqym.1      // ％PosixTime型で実際に格納されているデータは64bit整数
^poCN.Dqym.1=1
^poCN.Dqym.1(1)=$lb(1154598405306846976,"2023-02-20 13:45:00")
ObjectScript
ObjectScript

現在日時を%PosixTime型で出力したり、%TimeStamp ⇔ %PosixTime 変換したい場合は以下のように行えます。

USER>write ##Class(%Library.PosixTime).CurrentTimeStamp()
1154596073773251031
USER>write ##Class(%Library.PosixTime).LogicalToTimeStamp(ptime)
2023-01-24 14:06:06.404055
USER>write ##Class(%Library.PosixTime).TimeStampToLogical("2023-01-24 14:06:06.404055")
1154596073773251031ObjectScript
ObjectScript

詳細は以下のドキュメントをご覧ください
日付、時刻、PosixTime、およびタイムスタンプのデータ型

【ご参考】
%TimeStamp型プロパティを使用した範囲指定のクエリが遅い場合の対処方法
 SQLベースのベンチマークを行う際に、実施していただきたい5つの項目