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
などとすればよいです。
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