Persistent ODBCとOracle DatabseのDate型

HaskellのPersistentを利用してOracleデータベースのDate型へアクセスする。

Date型とTIMESTAMP型

DATE型は、日付および時刻情報(年、時、秒)が格納されるとのこと、詳しくは(DATEデータ型を参照)
TIMESTAMPデータ型、日付および時刻情報(年、時、秒、少数秒)が格納されDATE型の拡張となっている。

Persistent-ODBCから利用する

Haskell側のPersistentのモデル定義

OraMember
    regDate UTCTime Maybe
    deriving Typeable Show

データベース側テーブル

CREATE TABLE "DEVLOVE"."ORA_MEMBER" 
  ( "ID" NUMBER NOT NULL
  , "REG_DATE" DATE
  , PRIMARY KEY ("ID")
  );

CREATE SEQUENCE "seq_ORA_MEMBER_id";

SELECTする場合

SELECTする場合、 NLS_DATE_FORMAT をセッション中に一時的に変更しつつ、UTCTime の DiffTime 部分を 0～86399 で少数部は 0 に設定しパラメータに渡す。

alterNSLDateTimeSession :: MonadIO m => ReaderT SqlBackend m ()
alterNSLDateTimeSession = rawExecute "ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'" []

selectDate :: IO ()
selectDate = do
  now <- getCurrentTime
  runSQLAction $ do
    alterNSLDateTimeSession -- ここ
    let baseQuery = E.from $ \oraMember -> do
          E.where_ $ oraMember E.^. OraMemberRegDate E.>=. (E.just $ E.val (UTCTime (utctDay now) 10.0)) -- ここ
          return oraMember
    list <- E.select baseQuery
    mapM_ (\v -> liftIO $ print $ show v) list
    return ()

ORA-01861 が発生する原因

このエラーは ORA-01861: リテラルが書式文字列と一致しません とのことで NLS_DATE_FORMAT が適切に設定されていない場合に発生する。

OraCom: SqlError {seState = "[\"HY000\"]", seNativeError = -1, seErrorMsg = "execute execute: [\"1861: [Oracle][ODBC][Ora]ORA-01861: literal does not match format string\\n\"]"}

Oracleデータベース側のディフォルトの NLS_DATE_FORMAT の設定が会っていないことが原因

SQL> SELECT * FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT';

PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_DATE_FORMAT
DD-MON-RR

DD-MON-RR になっているフォーマットが問題らしいので、セッションの間だけ alterNSLDateTimeSession 設定を変えて対応する。

ORA-01830 が発生する原因

このエラーは、 ORA-01830: 日付書式の変換で不要なデータが含まれています とのことで、 Haskellの UTCTime の値（精度）とデーターベース側のDate型の精度と書式との関連で発生する。

getCurrentTime で取得した UTCTime の DiffTime 部は実際には、 Pico 型の精度 \(10^{-12}\) となっているため少数点以下を 0 に設定する必要がある。

OraCom: SqlError {seState = "[\"HY000\"]", seNativeError = -1, seErrorMsg = "execute execute: [\"1830: [Oracle][ODBC][Ora]ORA-01830: date format picture ends before converting entire input string\\n\"]"}

解決方法は、 UTCTime の DiffTime 部を0に設定する。

UTCTime (utctDay now) 0

補足

SYSDATEは正常にINSERTが完了

INSERT INTO ORA_MEMBER (ID, REG_DATE) VALUES
  ( "seq_ORA_MEMBER_id".NEXTVAL
  , TO_DATE(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')
  );

SYSTIMESTAMPは秒以下の情報を持っているため ORA-01830 のエラーが発生

INSERT INTO ORA_MEMBER (ID, REG_DATE) VALUES
  ( "seq_ORA_MEMBER_id".NEXTVAL
  , TO_DATE(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS')
  );
エラー・レポート -
ORA-01830: 日付書式の変換で不要なデータが含まれています

SYSTIMESTAMPが秒以下の情報を含んでいる。

INSERTする場合

SELECT の時と同様に NLS_DATE_FORMAT を調整しINSERTする。

insertOraMember :: IO ()
insertOraMember = do
  runSQLAction $ do
    alterNSLDateTimeSession
    now <- liftIO getCurrentTime
    _ <- insert $ OraMember
      { oraMemberRegDate = Just now
      }

INSERTの場合、DiffTimeが原因でエラーになる事はない、注意するところは

oraMemberRegDate = Just now

これでINSERTされた場合、 2021-06-28 13:22:59 のように時、分、秒まで保存される

oraMemberRegDate = Just (UTCTime day 0.0)

これでINSERTされた場合、 2021-06-28 00:00:00 で登録される、秒まで指定したい場合、SELECTの時と同様に DiffTime 部で調整する。

モデルにDay型が使えない

Day型をつかえなのか？ という素朴な疑問が沸くのですすが、 UTCTime を Day に変えて

OraMember
    regDate Day Maybe
    deriving Typeable Show

この場合、結果からいうと、 INSERTはどうにかなる が SELECTで上手くいかない という結果にはまる

INSERT

モデルの Day型 に合わせて INSERT 部は以下の用になる

oraMemberRegDate = Just (ModifiedJulianDay 0)

ユリウス日 で指定するため ModifiedJulianDay を利用し、モデルの型とマッチし、さらに PersistValue 型の PersistDay へ適合するのでデータベースへの保存は問題なく完了する、データの状態としては、時、分、秒のは0で保存される。

SELECT

その後にSELECTする場合、データベースの DATE型 を Persistent 経由で実行すると、 PersistValue 型の PersistUTCTime で取得されるため、モデルへのマッピングでランタイムエラーになる。

OraCom: PersistMarshalError "Couldn't parse field `regDate` from table `ORA_MEMBER`. Failed to parse Haskell type `Day`; expected day, integer, string or bytestring from database, but received: PersistUTCTime 1858-11-17 00:00:00 UTC. Potential solution: Check that your database schema matches your Persistent model definitions."

「Day型 は、interger か string が bytestring でよこせ、受け取ったのは PersistUTCTime 1858-11-17 00:00:00 UTC じゃないか、お前のモデルを調整してどうにかしろ」的なメッセージを受け取って不愉快になる。

この辺は、ライブラリでどうにかしろよと感じるところではありますが、 DATE型はUTCTimeで使うのが標準です と言われればそれまでとなりますので、ご注意ください。以上、HaskellでPersistent ODBCを利用した場合の、日付型取扱い時の備忘録となります。

Posted on 2021-06-28 08:08:04

魴鮄のブログ