YesodとPostgreSQLのtimestamp型

Tech > Haskell > データベース

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 で表記され日本時間は9時間ずれます。

PostgresSQLのtimestamp

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

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

p は精度で、 0から6 を指定可能(秒以下の精度)、 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

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

SQLによる検索

timestamptzでデータベースに保存されている時刻と、ユーザーが指定した日本時間や日時を比較する場合、データベースの時刻が9時間前の時刻になっているので、正確に比較できない場合があります、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

はじめまして

お茶の国静岡で、焼酎のお茶割なんか罰当たりで飲んだことはありません、常に一番搾りを嗜む静岡極東のBBQerです、最近まわりのエンジニアの方々がお料理を上手にやっている姿を恨めしそうに横目に見ながら、軟骨ピリ辛チクワを食べています、みなさんよろしく。

Posted

Amazon

tags

epub mobi calibre mask lens ワンライナー php redmine Linux Oracle Map OMap omap map BBQ カテゴリ管理 カテゴリ timestamp date oracle database string 麦焼酎 ダービー process 磨き蒸留 広井酒店、日本酒 芋焼酎 焼酎 ゆるキャン 広井酒店、日本酒池 spring framework java persistent spring session session spring hdbc-odbc persistent-odbc odbc day utctime スィート レマンの森 elm初期化 elm バイク xlr80 esqueleto database xl2tpd strongswan vpn l2tp ipsec 正月 ゲーム grub nginx systemctl portage 豚骨 圧力鍋 yesod-auth-hashdb yesod-auth yesod CentOS8 tsl ssl 設定 install gentoo ghci stackコマンド stack MathJax 数式 templete-haskell haskell とんこつラーメン プレビュー チャーシュウ python インストール CentOS7 ラーメン Houbou 置換タグ