月をまたぐと日が取れない（AthenaSQL）

Question

AthenaSQLで二週間前から一週間前までのデータを取得しようとしたら
7日前→12日前までは取得できたのですがこれを14日前や13日前にすると空白のカラムが現れてしまいました。
確認すると丁度月をまたぐタイミングでした。
これについてアドバイスを頂戴できますと幸いです

with provisional as(
SELECT DATE_FORMAT(CAST(year || '-' || month || '-' || day || ' ' || substring(time,8,5) as timestamp),'%Y/%m/%d %H:%i') AS tm
        , ip
        , id
        , quer
        , resp
FROM ～～～～
WHERE   and code='200'
AND "year" >= DATE_FORMAT(now() - interval '14' day,'%Y')
AND "year" < DATE_FORMAT(now() - interval '7' day,'%Y')
AND "month" >= DATE_FORMAT(now() - interval '14' day,'%m')
AND "month" < DATE_FORMAT(now() - interval '7' day,'%m')
AND  "day" >= DATE_FORMAT(now() - interval '14' day,'%d')--12にすると12日前は表示。
AND  "day" < DATE_FORMAT(now() - interval '7' day,'%d')
group by DATE_FORMAT(CAST(year || '-' || month || '-' || day || ' ' || substring(time,8,5) as timestamp),'%Y/%m/%d %H:%i')
        , xip
        , id
        , quer
        , resp
order by tm
)
select tm, avg(resp) AS resp
from provisional
group by tm
order by tm

Comments

[Orlofsky]

[MySQL]タグって関係あるの？

[narudake]

SQLでよかったですかね。。。
MySQL使ってる人が多そうなんでつけました。

Answer 1

AND DATE_FORMAT(CAST(year || '-' || month || '-' || day || ' ' || substring(time,8,5) as timestamp),'%Y/%m/%d %H:%i') >= DATE_FORMAT(now() - interval '14' day,'%Y/%m/%d')
AND DATE_FORMAT(CAST(year || '-' || month || '-' || day || ' ' || substring(time,8,5) as timestamp),'%Y/%m/%d %H:%i') < DATE_FORMAT(now() - interval '7' day,'%Y/%m/%d')

Comments

[sazi]

解決して良かったですね。
コードで括って見易すくすると、後学者の為になります。
「コードを下さい。」だけでなく、自力でたどり着いて報告したという事で評価しておきます。
※当たり前の事なので、今回だけですけど。

[narudake]

あたたかいお言葉をありがとうございます！

Answer 2

AND  DATE_FORMAT(CAST(year || '-' || month || '-' || day || ' ' || substring(time,8,5) as timestamp),'%Y/%m/%d %H:%i') >= DATE_FORMAT(now() - interval '14' day,'%Y/%m/%d')
AND  DATE_FORMAT(CAST(year || '-' || month || '-' || day || ' ' || substring(time,8,5) as timestamp),'%Y/%m/%d %H:%i') < DATE_FORMAT(now() - interval '7' day,'%Y/%m/%d')

Answer 3

year, month, dayがそれぞれで表しているのですから当然そうなると思います。
year, month, dayを結合し、日付型にして比較するべきでしょう

Comments

[narudake]

ありがとうございます！システム上一次テーブルがつくれないのですがそれでも可能でしょうか。
よろしくお願いします。

[sazi]

テーブルは関係ないです。
where 条件でyear, month, dayを使用して日付型に変換したものと比較すれば良いだけです。

[narudake]

convertでしょうか。。。
申し訳ございません。もしよければクエリを
ご教示いただくことは可能でしょうか。
どうぞよろしくお願いします。

[narudake]

ありがとうございます！

AND DATE_FORMAT(CAST(year || '-' || month || '-' || day || ' ' || substring(time,8,5) as timestamp),'%Y/%m/%d %H:%i') >= DATE_FORMAT(now() - interval '14' day,'%Y/%m/%d')
AND DATE_FORMAT(CAST(year || '-' || month || '-' || day || ' ' || substring(time,8,5) as timestamp),'%Y/%m/%d %H:%i') < DATE_FORMAT(now() - interval '7' day,'%Y/%m/%d')

これで解決できました！