YesodとPostgreSQLのtimestamp型

YesodというかHaskellとPersistentを利用してデータベースの日付を扱うときに少し混乱するのでメモ。

UTCTime

Yesodで時間を扱う場合ほぼ、 UTC (Universal time coordinated)協定世界時を使います。これは GMT (Greenwich mean time」)グリニッジ標準時より精度の高い表現だそうですが、使う側からしたらあまり関係ありません。

UTCTimeについては、UTCTimeとDay をご参照ください。

ZonedTime

UTCTimeにソーンの情報はありません、ゾーンの時間を扱いたい場合は、ZonedTimeを使います。

Prelude Data.Time> ut <- getCurrentTime
Prelude Data.Time> ut
2021-09-07 09:08:21.246344625 UTC
Prelude Data.Time> zt <- getZonedTime
Prelude Data.Time> zt
2021-09-07 18:08:31.694960999 JST

JST で表記され日本時間は９時間ずれます。

PostgresSQLのtimestamp

PostgresSQLの日付・時刻型のデータ型にtimestampがあります、２つの種類を使い分けないといけません。

宣言	サイズ	説明	精度
timestamp [ (p) ] [ without time zone ]	8バイト	日付と時刻両方（時間帯なし）	1マイクロ秒
timestamp [ (p) ] with time zone	8バイト	日付と時刻両方、時間帯付き	1マイクロ秒

p は精度で、 ０から６ を指定可能(秒以下の精度)、 with time zone の場合、PostgreSQLなら timestamptz と表記することができます。

テーブルの定義を行うときに、時間帯なし、時間帯あり、を決める必要があります。

CREATE SEQUENCE tbl_test_id_seq
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    START WITH 1
    CACHE 1
    NO CYCLE
    OWNED BY NONE;

CREATE TABLE tbl_test (
    id bigint NOT NULL DEFAULT nextval('tbl_test_id_seq'::regclass),
    tz timestamp with time zone NOT NULL,
    notz timestamp without time zone NOT NULL,
    CONSTRAINT tbl_test_pkey PRIMARY KEY (id)
);

tzは時間帯あり、notzは時間帯なしです、PostgreSQL自体は内部的にUTCを利用して保存します、時間帯ありフィールドが出力される際に postgresql.conf にかかれた timezone の設定で出力が調整されます、時間帯なしの場合は保存されているUTC値がそのまま出力されます。

抜粋..
timezone = 'Asia/Tokyo'
..

houbou=> select * from tbl_test;
 id |              tz               |            notz            
----+-------------------------------+----------------------------
  1 | 2021-09-07 18:05:40.497736+09 | 2021-09-07 09:05:40.497736

+09 がJSTの時間帯情報です、 Europe/London 辺りにすると +01 で出力されます。

Yesodから利用する

Yesodを利用して、先の定義のテーブルに時間を入れてみるサンプルです、UTCTimeをそのまま両レコードにいれるパターンと、ZonedTimeを時間帯にUTCを利用して変換したUTCTimeを(ZonedTimeをそのまま時間帯を考慮せず変換したもの)保存したサンプルです。

insco :: Handler ()
insco = runDB $ do
  now <- liftIO getTm
  zt <- liftIO getZonedTime
  _ <- insert $ TblTest now now
  _ <- insert $ TblTest (zonedTimeToUTC zt) (localTimeToUTC utc (zonedTimeToLocalTime zt))
  return ()

sql=> select * from tbl_test;
 id |              tz               |            notz            
----+-------------------------------+----------------------------
  1 | 2021-09-07 18:05:40.497736+09 | 2021-09-07 09:05:40.497736
  2 | 2021-09-07 18:05:40.497737+09 | 2021-09-07 18:05:40.497737
(2 行)

国際化を意識したアプリケーションの場合、ディフォルトでデータベースはtimestamptzで定義しアプリケーションはUTCTimeを利用した方がスマートかなと思います。

それとは違い、かなりローカルな感じのアプリケーションなら、timestampを利用しローカルな時間帯で保存した方が変換手間がないことがメリットかもしれません。

そして、後は意外と知らずにハマっていたことを書きます。

Date型に注意

これは、UTCTimeのdayをそのままデータベースに登録した場合

(UTCTime day _) <- getCurrentTime

これは、日本時間のサーバーで実行した場合、９時間前の値を取得するので、０時から９時の間でやると１日ずれた日になってしまいます。

SQLによる検索

timestamptzでデータベースに保存されている時刻と、ユーザーが指定した日本時間や日時を比較する場合、データベースの時刻が９時間前の時刻になっているので、正確に比較できない場合があります、SQL的には問題ないので一見分かりません。

where句などの比較するレコードに、時間帯情報をつけて検索してやります。

... where timezone('JST', acc_time) >= ?

ちなみに JST という日本時間を汎用的にとるには

Prelude Data.Time> tz <- getCurrentTimeZone
Prelude Data.Time> tz
JST
Prelude Data.Time> :i TimeZone
type TimeZone :: *
data TimeZone
  = TimeZone {timeZoneMinutes :: Int,
              timeZoneSummerOnly :: Bool,
              timeZoneName :: String}
Prelude Data.Time> fromIntegral (timeZoneMinutes tz) / 60
9.0

を利用します、PCに設定されているロケールでTimeZoneを返してきます。

Posted on 2021-09-07 18:12:54

魴鮄のブログ