【PostgreSQL】DATE / DATETIME / TIMESTAMP

特徴の違い
Timestampを日付でグループ化したい
Timestampな各日付における最終レコードの値を取得したい
1. アプリ側で実装した場合
JavaにおけるPostgreSQLのTIMESTAMPのマッピング

特徴の違い

	出力	サポート範囲(MySQL)	サポート範囲(PostgreSQL)
DATE	YYYY-MM-DD	1000-01-01 ~ 9999-12-31	4713BC ~ 5874897 AD
DATETIME	YYYY-MM-DD hh:mm:ss	1000-01-01 00:00:00 ~ 9999-12-31 00:00:00	なし
TIMESTAMP	YYYY-MM-DD hh:mm:ss	1970-01-01 00:00:00 ~ 2038-01-19 03:14:07	4713BC ~ 294276AD

MySQLではDATETIME / TIMESTAMPを利用することができますが、サポート範囲がTIMESTAMPでは長期サポートが想定されるソフトウェアでは心許ないので、DATETIMEを使用する方がいいでしょう。

DATEは日付情報のみが必要な場合に利用します。

以降ではPostgreSQLでの例を示します。

Timestampを日付でグループ化したい

PostgreSQLではTimestampを直接dateにcastできますので、以下のようにdateにキャストした列でグループ化することで実現できます。

select timestamp_column::date from some_table group by timestamp_column::date;

Timestampな各日付における最終レコードの値を取得したい

上記の”Timestampを日付でグループ化したい”で説明した方法を利用します。

一度Timestampを日付に集約し、最大のtimestampを求めるサブクエリを作成します。その後、そのtimestampを持つレコードを抽出します。

select * from some_table where timestamp in
(select max(timestamp) from
(select timestamp::date as timestamp_column, timestamp from some_table) mytable
group by timestamp_column);

アプリ側で実装した場合

ちなみに一旦全てのデータを取得してきてから、アプリ側で実装すると以下のようになります。（Kotlin）

getAllDatas()
    .groupBy { data -> data.timestamp.toLocalDateTime().truncatedTo(ChronoUnit.DAYS) }
    .map { entry -> entry.value.maxBy { data -> data.timestamp } }

なお、DB側からアプリ側へのデータ移行が必要なため、レコード数が多い場合には上記のDB側で選択し必要なレコードのみ渡す処理よりも応答性が悪くなります。

JavaにおけるPostgreSQLのTIMESTAMPのマッピング

timestamp without time zone

timestamp without time zoneのマッピングはjava.sql.Timestampではなく、java.time.LocalDateTimeを使用した方がいいです。

理由としてはPostgreSQLに保存される値がシステム自身のTimeZoneの影響を受けるためです。テーブル定義は以下とし、タイムゾーンなしで常にUTCで保存したいとします。

CREATE TABLE timestamps(
     timestamp timestamp(6) NOT NULL
);

例えば以下のように実行したとします。（Kotlinなのは勘弁してください）

val currentTime = System.currentTimeMillis()　//これはUTC
val timestamp = Timestamp(currentTime)       //UTCからTimestamp作成

println("currentTime:$currentTime")
println("timestamp.toString():${timestamp.toString()}")
println("timestamp.toGMTString():${timestamp.toGMTString()}")
println("timestamp.toLocaleString():${timestamp.toLocaleString()}")
println("timestamp:${timestamp.time}")

println("stored from database")
dao.insertTimestamp(timestamp)
dao.getLatestInsertedTimestamp().also {
    println("timestamp.toString():${it.timestamp.toString()}")
    println("timestamp.toString():${it.timestamp.time}")
}

val localDateTime = LocalDateTime.ofEpochSecond(timestamp.time / 1000, timestamp.nanos, ZoneOffset.UTC)
dao.insertLocalDateTime( localDateTime  ) 
rateDao.getLatestInsertedLocalDateTime () .also {
    println("localDateTime.toString():${it.timestamp}")
println("localDateTime.localDateTime.toEpochSecond(ZoneOffset.UTC):${it.timestamp.toEpochSecond(ZoneOffset.UTC) * 1000 + it.timestamp.nano / 1000000}")
}

この時の実行結果は以下になります。

currentTime:1670060051609
 timestamp.toString():2022-12-03 18:34:11.609
 timestamp.toGMTString():3 Dec 2022 09:34:11 GMT
 timestamp.toLocaleString():2022/12/03 18:34:11
 timestamp:1670060051609
 stored from database
 timestamp.toString():2022-12-03 18:34:11.609
 timestamp.toString():1670060051609
 localDateTime.toString():2022-12-03T09:34:11.609
 localDateTime.localDateTime.toEpochSecond(ZoneOffset.UTC):1670060051609

そもそもjava.sql.TimestampのtoStringがシステムのTimeZoneを反映して、JST+9の値が表示されています。ただ内部的にはunix timestampを正しく保持しているので、 java.sql.Timestamp とjava.time.LocalDateTime のどちらでも問題ないように見えます。

問題なのはPostgreSQLへの保存のされ方です。保存されたPostgreSQLのデータを確認すると以下のように表示されます。

期待するのはUTC時刻での保存なので、先行して入力した java.sql.Timestamp は期待した動きではありません。おそらくtimestamp.toString()の文字列リテラルを用いてマッピングするためです。

timestamp with time zone

timestamp with time zoneのマッピングはjava.sql.Timestampで問題ないです。java側での利便性を考えてOffsetDateTimeを使用しても問題ありません。

CREATE TABLE timestamps(
     timestamp timestamp(6) with time zone NOT NULL,
);

この場合、システムの持つタイムゾーンを考慮してUTCで保存することが可能です。

原則としてtimestamp with time zoneを使用する

表題通りで原則はこの通りです。わざわざtime zone情報を捨てる理由はありません。UTCでの使用を暗黙的に周知していたとしても、+00:00をつけるだけでそれを強制できるならすべきです。

PostgreSQLのtimezoneの確認方法

ちなみにPostgreSQLのtimezoneは以下のコマンドで確認できます。

show timezone;

PostgreSQLのtimezoneの変更方法

DB単位で変更するのは以下を使用します。

alter database database_name set timezone to 'Asia/Tokyo'

基本的にPostgreSQL全体で変更を適用できるように/var/lib/postgresql/data/postgresql.conf内の以下を変更します

timezone = 'Asia/Tokyo'
log_timezone = 'Asia/Tokyo'

dockerの場合はentry pointに以下の二つを追加する必要があります

environment:
  - TZ=Asia/Tokyo
  - PGTZ=Asia/Tokyo

Intellijのデータベースを使用している場合

IntellijのDatabase Toolはjdbcドライバー単体から動作環境のTimeZoneを取得できないため、自分で設定する必要があります。

これを設定しないと、いくらDockerやSessionのTimezoneを変えても設定したTimezoneで表示してくれません。注意してください。