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

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

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

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

```bash: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
```

で、こんな感じ。

```text: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* までの要件で検索しようとすると、

```sql: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
;
```

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

```text: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` 型はタイムゾーン持ってない[^1]ので、
検索対象のカラムのタイムゾーンちょっとずらして検索かけてやると

[^1]: タイムゾーン持ってる `timestamptz` 型っていうのが別にある

```sql: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
;
```

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

```text: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時間ずれてちょうどいい感じになる。