日付またぎの境界をtimezoneをずらして検索する

日次データの区切りが 前日の AM 5:00 から 当日の AM 5:00 まで

みたいなケースってよくあるじゃないですか。

とりあえずテストデータ投入

bash

{
    cat <<-EOD
set timezone to 'Asia/Tokyo';

DROP TABLE IF EXISTS hoge;
CREATE TABLE hoge (
    i int,
    d timestamp
);
EOD

    echo 'INSERT INTO hoge VALUES'
    for x in {-48..0}; do
        echo "($x, CURRENT_TIMESTAMP + interval '$x hour')"
    done | paste -s -d ','
    echo ";"

    echo "SELECT * FROM hoge;"
} | psql

で、こんな感じ。

result

  i  |             d
-----+----------------------------
 -48 | 2018-03-04 16:52:46.826483
 -47 | 2018-03-04 17:52:46.826483
 -46 | 2018-03-04 18:52:46.826483
 -45 | 2018-03-04 19:52:46.826483
 -44 | 2018-03-04 20:52:46.826483
 -43 | 2018-03-04 21:52:46.826483
 -42 | 2018-03-04 22:52:46.826483
 -41 | 2018-03-04 23:52:46.826483
 -40 | 2018-03-05 00:52:46.826483
 -39 | 2018-03-05 01:52:46.826483
 -38 | 2018-03-05 02:52:46.826483
 -37 | 2018-03-05 03:52:46.826483
 -36 | 2018-03-05 04:52:46.826483
 -35 | 2018-03-05 05:52:46.826483
 -34 | 2018-03-05 06:52:46.826483
 -33 | 2018-03-05 07:52:46.826483
 -32 | 2018-03-05 08:52:46.826483
 -31 | 2018-03-05 09:52:46.826483
 -30 | 2018-03-05 10:52:46.826483
 -29 | 2018-03-05 11:52:46.826483
 -28 | 2018-03-05 12:52:46.826483
 -27 | 2018-03-05 13:52:46.826483
 -26 | 2018-03-05 14:52:46.826483
 -25 | 2018-03-05 15:52:46.826483
 -24 | 2018-03-05 16:52:46.826483
 -23 | 2018-03-05 17:52:46.826483
 -22 | 2018-03-05 18:52:46.826483
 -21 | 2018-03-05 19:52:46.826483
 -20 | 2018-03-05 20:52:46.826483
 -19 | 2018-03-05 21:52:46.826483
 -18 | 2018-03-05 22:52:46.826483
 -17 | 2018-03-05 23:52:46.826483
 -16 | 2018-03-06 00:52:46.826483
 -15 | 2018-03-06 01:52:46.826483
 -14 | 2018-03-06 02:52:46.826483
 -13 | 2018-03-06 03:52:46.826483
 -12 | 2018-03-06 04:52:46.826483
 -11 | 2018-03-06 05:52:46.826483
 -10 | 2018-03-06 06:52:46.826483
  -9 | 2018-03-06 07:52:46.826483
  -8 | 2018-03-06 08:52:46.826483
  -7 | 2018-03-06 09:52:46.826483
  -6 | 2018-03-06 10:52:46.826483
  -5 | 2018-03-06 11:52:46.826483
  -4 | 2018-03-06 12:52:46.826483
  -3 | 2018-03-06 13:52:46.826483
  -2 | 2018-03-06 14:52:46.826483
  -1 | 2018-03-06 15:52:46.826483
   0 | 2018-03-06 16:52:46.826483
(49 rows)

真面目にやると

で、ここから 2018-03-05 分として
2018-03-05 05:00:00 から 2018-03-06 05:00:00 までの要件で検索しようとすると、

psql

\set target_date '2018-03-05'

SET timezone TO 'Asia/Tokyo';

SELECT
  i
  , d
FROM hoge
WHERE
  (:'target_date'::DATE || ' 05:00:00')::TIMESTAMP <= d
  AND d < ((:'target_date'::DATE + interval '+1 day')::DATE || ' 05:00:00')::TIMESTAMP
;

みたいな感じで頑張るわけで、積み重なってくるとけっこうつらい。

result

  i  |             d
-----+----------------------------
 -35 | 2018-03-05 05:52:46.826483
 -34 | 2018-03-05 06:52:46.826483
 -33 | 2018-03-05 07:52:46.826483
 -32 | 2018-03-05 08:52:46.826483
 -31 | 2018-03-05 09:52:46.826483
 -30 | 2018-03-05 10:52:46.826483
 -29 | 2018-03-05 11:52:46.826483
 -28 | 2018-03-05 12:52:46.826483
 -27 | 2018-03-05 13:52:46.826483
 -26 | 2018-03-05 14:52:46.826483
 -25 | 2018-03-05 15:52:46.826483
 -24 | 2018-03-05 16:52:46.826483
 -23 | 2018-03-05 17:52:46.826483
 -22 | 2018-03-05 18:52:46.826483
 -21 | 2018-03-05 19:52:46.826483
 -20 | 2018-03-05 20:52:46.826483
 -19 | 2018-03-05 21:52:46.826483
 -18 | 2018-03-05 22:52:46.826483
 -17 | 2018-03-05 23:52:46.826483
 -16 | 2018-03-06 00:52:46.826483
 -15 | 2018-03-06 01:52:46.826483
 -14 | 2018-03-06 02:52:46.826483
 -13 | 2018-03-06 03:52:46.826483
 -12 | 2018-03-06 04:52:46.826483
(24 rows)

そこで

postgresql の timestamp 型はタイムゾーン持ってない¹ので、
検索対象のカラムのタイムゾーンちょっとずらして検索かけてやると

psql

\set target_date '2018-03-05'

SET timezone TO 'Asia/Tokyo';

SELECT
  i
  , d
  , (d AT TIME ZONE 'Etc/GMT-14')::DATE AS boundary_AM5_date
FROM hoge
WHERE
  (d AT TIME ZONE 'Etc/GMT-14')::DATE = :'target_date'::DATE
;

範囲検索使わなくても検索できる。

result

  i  |             d              | boundary_am5_date
-----+----------------------------+-------------------
 -35 | 2018-03-05 05:52:46.826483 | 2018-03-05
 -34 | 2018-03-05 06:52:46.826483 | 2018-03-05
 -33 | 2018-03-05 07:52:46.826483 | 2018-03-05
 -32 | 2018-03-05 08:52:46.826483 | 2018-03-05
 -31 | 2018-03-05 09:52:46.826483 | 2018-03-05
 -30 | 2018-03-05 10:52:46.826483 | 2018-03-05
 -29 | 2018-03-05 11:52:46.826483 | 2018-03-05
 -28 | 2018-03-05 12:52:46.826483 | 2018-03-05
 -27 | 2018-03-05 13:52:46.826483 | 2018-03-05
 -26 | 2018-03-05 14:52:46.826483 | 2018-03-05
 -25 | 2018-03-05 15:52:46.826483 | 2018-03-05
 -24 | 2018-03-05 16:52:46.826483 | 2018-03-05
 -23 | 2018-03-05 17:52:46.826483 | 2018-03-05
 -22 | 2018-03-05 18:52:46.826483 | 2018-03-05
 -21 | 2018-03-05 19:52:46.826483 | 2018-03-05
 -20 | 2018-03-05 20:52:46.826483 | 2018-03-05
 -19 | 2018-03-05 21:52:46.826483 | 2018-03-05
 -18 | 2018-03-05 22:52:46.826483 | 2018-03-05
 -17 | 2018-03-05 23:52:46.826483 | 2018-03-05
 -16 | 2018-03-06 00:52:46.826483 | 2018-03-05
 -15 | 2018-03-06 01:52:46.826483 | 2018-03-05
 -14 | 2018-03-06 02:52:46.826483 | 2018-03-05
 -13 | 2018-03-06 03:52:46.826483 | 2018-03-05
 -12 | 2018-03-06 04:52:46.826483 | 2018-03-05
(24 rows)

っていう話。

Amazon Redshift でもたぶん使える、と思う。

なんでこれでOKか

こういう対応。

Asia/Tokyo	UTC	Etc/GMT-14
2018-03-06 05:52:46	2018-03-06 14:52:46	2018-03-06 00:52:46

• Asia/Tokyo が +09:00

なので、

• Etc/GMT-14 の +14:00（ここ名前に反して - じゃなくて + なのがややこしいので注意）

を指定してやると、日本時間から-5時間ずれてちょうどいい感じになる。

---

1. タイムゾーン持ってる timestamptz 型っていうのが別にある ↩