JST基準で日別にデータを取得したい

Question

以下の様な形式でSQLで日別ののデータ数を取得しようとしています。

日付	データ数
2020/08/20	8
2020/08/21	3
2020/08/22	5
・・・

データカウントの対象であるカラム（created_at）はdatetime型かつUTCになっているのですが、
これをJST基準で、日別に計測したいと考えています。

いったん以下の様なクエリを書いてみまして、一見うまく取得できた様に思っていたのですが、
改めて検算してみたところ、UTCベースの日別データ数が取得されてしまいました。

SELECT DATE_FORMAT(created_at, '%Y/%m/%d') AS "日付", COUNT(*) AS "データ数" 
FROM table 
WHERE created_at BETWEEN '2020-08-19 15:00:00' AND '2020-09-11 14:59:59' 
GROUP BY 日付;

DATE_FORMATを使ったタイミングでUTCベースの日付なってしまっているんだろうなというのは予測がついているのですが、
代替としてどういった関数を記述するべきかがわかっておりません。

どなたかご教示いただけると幸いです。よろしくお願いいたします。

Comments

[unhappychoice]

データベースの種類はどちらでしょうか。（ MySQL ? Postgres ? SQLite or other ?

[Orlofsky]

同じSQLでもデータベースやそのバージョンによって方言が大きいですから、どのデータベースを使うのかを質問のタグで示したり、バージョンも明記した方が適切なコメントが付き易いです。

[RoyHargrove]

失礼しました。MySQL8.0.21になります

Answer 1

MySQL であれば

SELECT DATE_FORMAT(CONVERT_TZ(created_at, '+00:00', '+09:00'), '%Y/%m/%d) AS"日付", COUNT(*) AS "データ数"

でしょうか

Comments

[RoyHargrove]

こちらの回答通りに実行することで解決できました。ありがとうございました！

Answer 2

質問に核心部分のタイムゾーンの変換部分だけ、
Oracle Database では

SQL> SELECT
  2     TO_CHAR(TO_TIMESTAMP_TZ('2020/09/12 23:10:20 UTC', 'YYYY/MM/DD HH24:MI:SS TZR') AT TIME ZONE 'Asia/Tokyo'
  3           , 'YYYY/MM/DD HH24:MI:SS TZR TZD') AS JST
  4  FROM DUAL;

JST
--------------------------------------------------------------------------------
2020/09/13 08:10:20 ASIA/TOKYO JST
コード