PostgreSQL日付操作まとめ #PostgreSQL

公式ドキュメント

キャスト

以下はどちらもまったく同じである：

SELECT '2023-09-15 12:34:56'::timestamp;
SELECT CAST('2023-09-15 12:34:56' AS timestamp);

タイムゾーン表示

タイムゾーンはセッション毎に設定される。

postgres=# show timezone;
 TimeZone
----------
 UTC

タイムゾーン変更

カレントセッションのみ変更

set timezone to 'Asia/Tokyo';

postgresql.confでグローバルに設定

timezone = 'Asia/Tokyo'

データベースごとにデフォルト値を設定

-- 変更
ALTER DATABASE db1 SET timezone TO 'Asia/Tokyo';
-- この後db1に接続し直すと反映される

日付のデータ型

timestamp with time zoneはtimestamptzと略記できる（PostgreSQLの拡張であり標準SQLには無い）。

timestampとtimestamptzの違い

timestamptzは現在のセッションのタイムゾーンに変換して表示される

postgres=# create table t (a timestamp, b timestamptz);
CREATE TABLE
時間: 6.300 ミリ秒
postgres=# insert into t (a, b) values ('2023-09-06 01:02:03', '2023-09-06 01:02:03+09:00');
INSERT 0 1
時間: 2.058 ミリ秒
postgres=# insert into t (a, b) values ('2023-09-06 01:02:03', '2023-09-06 01:02:03+03:00');
INSERT 0 1
時間: 1.199 ミリ秒
postgres=# select * from t;
          a          |           b
---------------------+------------------------
 2023-09-06 01:02:03 | 2023-09-06 01:02:03+09
 2023-09-06 01:02:03 | 2023-09-06 07:02:03+09
(2 行)

timestamptzからtimestampへのキャスト

単純にタイムゾーン部分が無視された日時となる。

postgres=# select * from t;
          a          |           b
---------------------+------------------------
 2023-09-06 01:02:03 | 2023-09-06 01:02:03+09
 2023-09-06 01:02:03 | 2023-09-06 07:02:03+09
(2 行)

postgres=# select a, b::timestamp from t;
          a          |          b
---------------------+---------------------
 2023-09-06 01:02:03 | 2023-09-06 01:02:03
 2023-09-06 01:02:03 | 2023-09-06 07:02:03
(2 行)

timestampからtimestamptzへのキャスト

単純に現在のセッションのタイムゾーンが付与される。

postgres=# select a::timestamptz, b from t;
           a            |           b
------------------------+------------------------
 2023-09-06 01:02:03+09 | 2023-09-06 01:02:03+09
 2023-09-06 01:02:03+09 | 2023-09-06 07:02:03+09
(2 行)

日付を文字列化

SELECT to_char(now(), 'YYYY-MM-DD HH24:MI:SS.US');

日付のパース

::timestamptzでキャストするのが簡単。

postgres=# SELECT '2021-02-03T04:05:06.789012+09:00'::timestamptz;
          timestamptz
-------------------------------
 2021-02-02 19:05:06.789012+00   -- DBのタイムゾーンはUTCなので、正常にパースできている

postgres=# SELECT '2021-02-03 04:05:06 +09:00'::timestamptz;
      timestamptz
------------------------
 2021-02-02 19:05:06+00   -- DBのタイムゾーンはUTCなので、正常にパースできている
(1 row)

postgres=# SELECT '2021-02-03 04:05:06 +09:00'::timestamp;
      timestamp
---------------------
 2021-02-03 04:05:06  -- +09:00が無視されてしまっている

postgres=# SELECT to_timestamp('2021-02-03T04:05:06+09:00', 'YYYY-MM-DD"T"HH24:MI:SS');
      to_timestamp
------------------------
 2021-02-03 04:05:06+00  -- +09:00が無視されてしまっている

日付の加算減算

SELECT now() + '3 days'::interval;

postgres=# select '2023-01-31'::date + '1 month'::interval;
      ?column?
---------------------
 2023-02-28 00:00:00  -- 2月31日は存在しないので月末になる
(1 行)

日付の差

単純に引き算すれば良い。結果はinterval型になる。

select '2023-09-05 12:00:00'::timestamp - '2023-09-05 12:00:00+09:00'::timestamp with time zone;

日付を切り詰める（date_trunc('second', timestamp)）

postgres=# select date_trunc('day', '2021/2/14 01:23:45.678912'::timestamp);
     date_trunc
---------------------
 2021-02-14 00:00:00
(1 row)

Time: 2.529 ms
postgres=# select date_trunc('second', '2021/2/14 01:23:45.678912'::timestamp);
     date_trunc
---------------------
 2021-02-14 01:23:45
(1 row)

Time: 1.151 ms
postgres=# select date_trunc('minute', '2021/2/14 01:23:45.678912'::timestamp);
     date_trunc
---------------------
 2021-02-14 01:23:00
(1 row)

interval型を秒に変換

postgres=# select extract(epoch from '1 hour'::interval);
 date_part
-----------
      3600

interval型を日数に変換

postgres=# SELECT floor(EXTRACT(epoch FROM (SELECT ('2023-09-15 12:00:00'::timestamp - '2023-09-13 19:00:00'::timestamp))) / 86400);
 floor
-------
     1  -- floorは小数点切り捨て

postgres=# SELECT ceil(EXTRACT(epoch FROM (SELECT ('2023-09-15 12:00:00'::timestamp - '2023-09-13 19:00:00'::timestamp))) / 86400);
 ceil
-------
     2  -- ceilは小数点切り上げ

timestamp型をUNIXエポック秒に変換

postgres=# select extract(epoch from '2023-09-05 12:00:00'::timestamp);
 date_part
------------
 1693915200