Redshiftで `EXTRACT(EPOCH FROM interval)` を使うと間違った値が返ってくる #AWS

tl;dr

Redshift内にある2つの時刻の差を整数値で求めたいとき
PostgreSQLの文法 EXTRACT(EPOCH FROM interval) を使うと エラーを吐かずに間違った値が返ってくる ので、

SELECT 
  EXTRACT(EPOCH FROM end_time - start_time) interval_sec
FROM hoge

ではなく、

SELECT
  DATEDIFF(second, start_time, end_time)
FROM hoge

を使いましょう。

発生した現象

Googleで、Redshiftのinterval型を整数値に変換したいと思って redshift interval to second というクエリで検索すると、
検索上位には

EXTRACT(EPOCH FROM interval) を使え

という記事が上位に出てきます。

これを使って時刻の差を「日数」の整数値にするSQLをいろいろ書いたのですが、結果を確認したところ、おかしい値が返ってくることに気づきました。
「ある日付 _date と2019年10月01日との差の日数」をいろんな日付で調べてみたところ…

select
  _date,
  '2019-10-01'::timestamp - _date _interval,
  extract(epoch from _interval) date_diff_in_sec,
  extract(epoch from _interval)/(60*60*24) date_diff_in_day
from interval_test -- <- '2019-09-30', '2019-09-29', ... '2019-08-01' が入っている
order by _date desc

_date	_interval	date_diff_in_sec	date_diff_in_day	正しい？
2019-09-30	1 day	86400	1	正しい
2019-09-29	2 days	172800	2	正しい
2019-09-28	3 days	259200	3	正しい
2019-09-27	4 days	345600	4	正しい
2019-09-26	5 days	432000	5	正しい
2019-09-25	6 days	518400	6	正しい
2019-09-24	7 days	604800	7	正しい
2019-09-23	8 days	691200	8	正しい
2019-09-22	9 days	777600	9	正しい
2019-09-21	10 days	864000	10	正しい
2019-09-20	11 days	950400	11	正しい
2019-09-19	12 days	1036800	12	正しい
2019-09-18	13 days	1123200	13	正しい
2019-09-17	14 days	1209600	14	正しい
2019-09-16	15 days	1296000	15	正しい
2019-09-15	16 days	1382400	16	正しい
2019-09-14	17 days	1468800	17	正しい
2019-09-13	18 days	1555200	18	正しい
2019-09-12	19 days	1641600	19	正しい
2019-09-11	20 days	1728000	20	正しい
2019-09-10	21 days	1814400	21	正しい
2019-09-09	22 days	1900800	22	正しい
2019-09-08	23 days	1987200	23	正しい
2019-09-07	24 days	2073600	24	正しい
2019-09-06	25 days	2160000	25	正しい
2019-09-05	26 days	2246400	26	正しい
2019-09-04	27 days	2332800	27	正しい
2019-09-03	28 days	2419200	28	正しい
2019-09-02	29 days	2505600	29	正しい
2019-09-01	30 days	5184000	60	+30日
2019-08-31	31 days	5270400	61	+30日
2019-08-30	32 days	5356800	62	+30日
2019-08-29	33 days	5443200	63	+30日
2019-08-28	34 days	5529600	64	+30日
2019-08-27	35 days	5616000	65	+30日
2019-08-26	36 days	5702400	66	+30日
2019-08-25	37 days	5788800	67	+30日
2019-08-24	38 days	5875200	68	+30日
2019-08-23	39 days	5961600	69	+30日
2019-08-22	40 days	6048000	70	+30日
2019-08-21	41 days	6134400	71	+30日
2019-08-20	42 days	6220800	72	+30日
2019-08-19	43 days	6307200	73	+30日
2019-08-18	44 days	6393600	74	+30日
2019-08-17	45 days	6480000	75	+30日
2019-08-16	46 days	6566400	76	+30日
2019-08-15	47 days	6652800	77	+30日
2019-08-14	48 days	6739200	78	+30日
2019-08-13	49 days	6825600	79	+30日
2019-08-12	50 days	6912000	80	+30日
2019-08-11	51 days	6998400	81	+30日
2019-08-10	52 days	7084800	82	+30日
2019-08-09	53 days	7171200	83	+30日
2019-08-08	54 days	7257600	84	+30日
2019-08-07	55 days	7344000	85	+30日
2019-08-06	56 days	7430400	86	+30日
2019-08-05	57 days	7516800	87	+30日
2019-08-04	58 days	7603200	88	+30日
2019-08-03	59 days	7689600	89	+30日
2019-08-02	60 days	10368000	120	+60日
2019-08-01	61 days	10454400	121	+60日

intervalが30日を超えるごとに、返ってくる日数が30日多くなる現象が発生しました。

原因

AWSサポートにお問い合わせしたところ、

調査したところ、現時点では Redshfit の EXTRACT 関数では timestamp に interval を指定した場合に不正確な結果を返すことがある動作であることがわかりました。
下記は Redshift の EXTRACT 関数に関するドキュメントでございますが、こちらから FROM 句以降には timestamp を指定する仕様であることをご確認いただけるかと存じます。

EXTRACT Function - Amazon Redshift > https://docs.aws.amazon.com/redshift/latest/dg/r_EXTRACT_function.html

Syntax
EXTRACT ( datepart FROM { TIMESTAMP 'literal' | timestamp } )

(中略)

なお、PostgreSQL 8.0 では extract 関数にて interval が使用可能であると存じます。

(中略)

担当部署においても、Redshift の EXTRACT 関数にて interval を指定した際に不正確な結果を返す動作であることを認識しており、正しく動作するように修正するか、または適切なエラーを返すよう修正を行う予定でございます。
しかしながら、担当部署でも調整もございますため、対応実施およびその時期につきましてはご案内出来兼ねますことを何卒ご理解いただきたく存じます。

との返答を頂きました。
すなわち、

EXTRACT(EPOCH FROM interval) はPostgreSQLの文法であってRedshiftでの動作は未定義
Redshiftで EXTRACT(EPOCH FROM interval) を使うと、エラーにならずに、なんらかの不正確な結果が返ってくる。
- 実際には30日未満のときは正確な結果が返ってくるが30日以上のときに不正確な結果が返ってくる
AWSとしては、正確な値を返すかエラーにするかしたいが時期は未定

ということだそうです。

解決方法

Redshiftでは、 EXTRACT(EPOCH FROM interval) の代わりに DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} ) という関数があり、こちらは2つの時刻/日付の差を正確な整数値で返すことが保証されています。
こちらを使いましょう。
例えば、日数が欲しい場合は

select
  _date,
  DATEDIFF(day, _date, '2019-10-01'::timestamp)
  date_diff_in_day
from interval_test -- <- '2019-09-30', '2019-09-29', ... '2019-08-01' が入っている
order by _date desc

などとすればよいです。

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_DATEDIFF_function.html

Googleの検索上位は一体……

冒頭で示した、redshift interval to second のGoogle検索したときに上位に出てくるStackOverflowは、よく見たら PostgreSQLの質問 で、Redshiftについての質問でないことにあとで気がつきました。
Google検索上位に出た記事を盲目的に信用せず、その記事に書いてある内容が自分の知りたい内容と合致しているのかちゃんと確認しましょう。

2つ目の記事は……、間違ったことが書いてありますね…

付録

再現用SQL

create table interval_test (date _date);
insert into interval_test
select '2019-08-01'::date _date
union all
select '2019-08-02'::date
union all
select '2019-08-03'::date
union all
select '2019-08-04'::date
union all
select '2019-08-05'::date
union all
select '2019-08-06'::date
union all
select '2019-08-07'::date
union all
select '2019-08-08'::date
union all
select '2019-08-09'::date
union all
select '2019-08-10'::date
union all
select '2019-08-11'::date
union all
select '2019-08-12'::date
union all
select '2019-08-13'::date
union all
select '2019-08-14'::date
union all
select '2019-08-15'::date
union all
select '2019-08-16'::date
union all
select '2019-08-17'::date
union all
select '2019-08-18'::date
union all
select '2019-08-19'::date
union all
select '2019-08-20'::date
union all
select '2019-08-21'::date
union all
select '2019-08-22'::date
union all
select '2019-08-23'::date
union all
select '2019-08-24'::date
union all
select '2019-08-25'::date
union all
select '2019-08-26'::date
union all
select '2019-08-27'::date
union all
select '2019-08-28'::date
union all
select '2019-08-29'::date
union all
select '2019-08-30'::date
union all
select '2019-08-31'::date
union all
select '2019-09-01'::date
union all
select '2019-09-02'::date
union all
select '2019-09-03'::date
union all
select '2019-09-04'::date
union all
select '2019-09-05'::date
union all
select '2019-09-06'::date
union all
select '2019-09-07'::date
union all
select '2019-09-08'::date
union all
select '2019-09-09'::date
union all
select '2019-09-10'::date
union all
select '2019-09-11'::date
union all
select '2019-09-12'::date
union all
select '2019-09-13'::date
union all
select '2019-09-14'::date
union all
select '2019-09-15'::date
union all
select '2019-09-16'::date
union all
select '2019-09-17'::date
union all
select '2019-09-18'::date
union all
select '2019-09-19'::date
union all
select '2019-09-20'::date
union all
select '2019-09-21'::date
union all
select '2019-09-22'::date
union all
select '2019-09-23'::date
union all
select '2019-09-24'::date
union all
select '2019-09-25'::date
union all
select '2019-09-26'::date
union all
select '2019-09-27'::date
union all
select '2019-09-28'::date
union all
select '2019-09-29'::date
union all
select '2019-09-30'::date;

select
  _date,
  '2019-10-01'::timestamp - _date _interval,
  extract(epoch from _interval) date_diff_in_sec,
  extract(epoch from _interval)/(60*60*24) date_diff_in_day_extract,
  datediff(day, _date, '2019-10-01'::timestamp) date_diff_in_day_datediff
from interval_test
order by _date desc