Postgresのtimestamp型の挙動まとめ

Postgresのtimestamp型を問題なく実装出来ているか？今一度以下の文章を見て再考頂きたい！
もしかしたら運用中のサービスにバグが見つかるかもしれませんよ！
※本件はPostgres14でやってますよ！

下準備

-- テーブルの作成
test=# create table test001 ( id integer, created_at timestamp);
CREATE TABLE

-- テスト用データのINSERT
test=# INSERT INTO test001 VALUES
test-# (1, TIMESTAMP '2022-07-01 00:00:00'),
test-# (2, TIMESTAMP '2022-07-01 00:00:00.000000'),
test-# (3, TIMESTAMP '2022-07-01 23:59:59'),
test-# (4, TIMESTAMP '2022-07-01 23:59:59.999999'),
test-# (5, TIMESTAMP '2022-07-02 00:00:00'),
test-# (6, TIMESTAMP '2022-07-02 00:00:00.000000');
INSERT 0 6

さてこの時点で

test-# SELECT * FROM test001;
test-# SELECT * FROM test001 WHERE created_at = '2022-07-01 00:00:00';
test-# SELECT * FROM test001 WHERE created_at = '2022-07-01 00:00:00.000000';
test-# SELECT * FROM test001 WHERE created_at = '2022-07-01 00:00:00.000001';
test-# SELECT * FROM test001 WHERE created_at = '2022-07-01 00:00:00.0000001';
test-# SELECT * FROM test001 WHERE created_at BETWEEN '2022-07-01 00:00:00' AND '2022-07-02';
test-# SELECT * FROM test001 WHERE created_at BETWEEN '2022-07-01 00:00:00' AND '2022-07-01 23:59:59.999999';

を見たらどうなっているか自信をもっていえますか？

答えが気になる方はスクロール
↓　↓　↓　↓　↓　↓　↓　↓　↓

↓　↓　↓　↓　↓　↓　↓　↓

↓　↓　↓　↓　↓　↓　↓

↓　↓　↓　↓　↓　↓

↓　↓　↓　↓　↓

↓　↓　↓　↓

↓　↓　↓

↓　↓

↓

答え合わせ

１つ目

test=# SELECT * FROM test001;
 id |         created_at
----+----------------------------
  1 | 2022-07-01 00:00:00
  2 | 2022-07-01 00:00:00
  3 | 2022-07-01 23:59:59
  4 | 2022-07-01 23:59:59.999999
  5 | 2022-07-02 00:00:00
  6 | 2022-07-02 00:00:00
(6 行)

ポイントはマイクロ秒の部分が全て0の場合は省略されて、１と２が同じ値となっている所でしょうか。
これは表示上そうなってるだけで実際はマイクロ秒も000000と６桁の0が入っています。
つまりIDの１と２は同じcreated_atという事です。
それを証明するのが次の答えになります。

２つ目～５つ目

test=# SELECT * FROM test001 WHERE created_at = '2022-07-01 00:00:00';
 id |     created_at
----+---------------------
  1 | 2022-07-01 00:00:00
  2 | 2022-07-01 00:00:00
(2 行)

test=# SELECT * FROM test001 WHERE created_at = '2022-07-01 00:00:00.000000';
 id |     created_at
----+---------------------
  1 | 2022-07-01 00:00:00
  2 | 2022-07-01 00:00:00
(2 行)

test=# SELECT * FROM test001 WHERE created_at = '2022-07-01 00:00:00.000001';
 id | created_at
----+------------
(0 行)

test=# SELECT * FROM test001 WHERE created_at = '2022-07-01 00:00:00.0000001';
 id |     created_at
----+---------------------
  1 | 2022-07-01 00:00:00
  2 | 2022-07-01 00:00:00
(2 行)

Postgresの公式サイトのTimestampの説明にも精度はマイクロ秒と記載されています。
そのため小数点６桁より後ろの数字の精度は保証されておらず、created_at = '2022-07-01 00:00:00.0000001'という条件は丸められて切り捨てられたんでしょうね。

ここで疑問がわいてきます。

え？じゃcreated_at = '2022-07-01 00:00:00.0000006' だったらどうなん？

はい、実はこうなります。

test=# EXPLAIN SELECT * FROM test001 WHERE created_at = '2022-07-01 00:00:00.0000006';
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Seq Scan on test001  (cost=0.00..1.07 rows=1 width=12)
   Filter: (created_at = '2022-07-01 00:00:00.000001'::timestamp without time zone)
(2 行)

test=# SELECT * FROM test001 WHERE created_at = '2022-07-02 00:00:00.0000006';
 id | created_at
----+------------
(0 行)

うぉー！！！実行計画で繰り上げられとるー！！当然０件となっとるー！！
8byteで表現できる精度の問題なんでしょうね。

６つ目、７つ目

ここまでくれば答えはわかるでしょうけど一応ね！

test=# SELECT * FROM test001 WHERE created_at BETWEEN '2022-07-01 00:00:00' AND '2022-07-02';
 id |         created_at
----+----------------------------
  1 | 2022-07-01 00:00:00
  2 | 2022-07-01 00:00:00
  3 | 2022-07-01 23:59:59
  4 | 2022-07-01 23:59:59.999999
  5 | 2022-07-02 00:00:00
  6 | 2022-07-02 00:00:00
(6 行)

test=# SELECT * FROM test001 WHERE created_at BETWEEN '2022-07-01 00:00:00' AND '2022-07-01 23:59:59.999999';
 id |         created_at
----+----------------------------
  1 | 2022-07-01 00:00:00
  2 | 2022-07-01 00:00:00
  3 | 2022-07-01 23:59:59
  4 | 2022-07-01 23:59:59.999999
(4 行)

指定してない所は0で埋められた感じで処理されるって考えればすごく真っ当な結果ですね！
強いて言うなら、BETWEENって「以上？より上？～以下？より下？」っていう疑問な人はいたんじゃないでしょうか。
BETWEENは両方「以上～以下」という条件になってしまう事に注意してください。
例えば2022年7月1日に作成されたレコードを取り出せと言われた時のSQLを考えた時にやってはいけないのは、以下になりますよ！

-- 2022-07-01 00:00:00のデータしか返らない
test=# SELECT * FROM test001 WHERE created_at BETWEEN '2022-07-01' AND '2022-07-01';

-- 2022-07-02 00:00:00のデータも含んでしまう
test=# SELECT * FROM test001 WHERE created_at BETWEEN '2022-07-01' AND '2022-07-02';

みなさんタイムスタンプは正しく使いましょう！

付録

記載した文章の中で0.0000006が繰り上げられたのを精度が原因としましたが、「いやぁこれは偶数丸めさ！」とか「五捨六入だぜ！」と思った方いるかもしれませんので、
一応以下のSQLの実行計画を貼っておきます。

-- 0000005 が繰り下げられている
test=# EXPLAIN SELECT * FROM test001 WHERE created_at = '2022-07-01 23:59:59.0000005';
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Seq Scan on test001  (cost=0.00..1.07 rows=1 width=12)
   Filter: (created_at = '2022-07-01 23:59:59'::timestamp without time zone)
(2 行)

-- 0000006 が繰り上げられている
test=# EXPLAIN SELECT * FROM test001 WHERE created_at = '2022-07-01 23:59:59.0000006';
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Seq Scan on test001  (cost=0.00..1.07 rows=1 width=12)
   Filter: (created_at = '2022-07-01 23:59:59.000001'::timestamp without time zone)
(2 行)

-- 0000004 が繰り下げられている
test=# EXPLAIN SELECT * FROM test001 WHERE created_at = '2022-07-01 23:59:59.9999994';
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Seq Scan on test001  (cost=0.00..1.07 rows=1 width=12)
   Filter: (created_at = '2022-07-01 23:59:59.999999'::timestamp without time zone)
(2 行)

-- 0000005 が繰り上げられている
test=# EXPLAIN SELECT * FROM test001 WHERE created_at = '2022-07-01 23:59:59.9999995';
                                 QUERY PLAN
-----------------------------------------------------------------------------
 Seq Scan on test001  (cost=0.00..1.07 rows=1 width=12)
   Filter: (created_at = '2022-07-02 00:00:00'::timestamp without time zone)
(2 行)