【MySQL】当日、数日前の日付をタイムゾーン変換する

#Abstract
以下の条件でテストデータを作成する。

Database Version: MySQL5.7
System Timezone: UTC(変更せず)
Local Timezone: JST
Volume：数百レコード
Requests：テストを1日で終わないため、いつでもINSERTする時、つねに当日(JST)、または当日から数日、数ヶ月前の指定時刻(yyyy-MM-dd 00:00:00)のTIMESTAMP型の日付でカラム(UTC)を更新する。
#Introduction
要望をよくみると、

1. (UTC)実行時の当日日付(e.g.yyyy-MM-02 12:59:59 UTC)を取得
2. (JST)指定時刻の当日日付yyyy-MM-02 00:00:00 JSTへ変換
3. (UTC)日付を変換し、yyyy-MM-01 15:00:00 UTCでINSERTする

という流れで明らかにした。

#Methods
##No.1
JSTはUTCより9時間進んでいることはさきに知っておけば、この方法が分かりやすくなると思われる。

当日

INSERT INTO `table_name` (colume_name) VALUES (TIMESTAMPADD(HOUR,-9,CONCAT(DATE(TIMESTAMPADD(HOUR,9,NOW())),' 00:00:00')));

数日前、数か月前
開始日付から減算される間隔値を指定する式は、DAYやMONTHなどを使用する。1日前を例として挙げる。下の方法も同様。

INSERT INTO `table_name` (colume_name) VALUES (TIMESTAMPADD(HOUR,-9,CONCAT(DATE(DATE_SUB(TIMESTAMPADD(HOUR,9,NOW()),INTERVAL 1 DAY)),' 00:00:00')));

##No.2
時差は調べたくない場合は、'+00:00'を'UTC'に、'+09:00'を'Asia/Tokyo'に書き換えられる。ただし、適切に設定する必要がある。

当日

INSERT INTO `table_name` (colume_name) VALUES (CONVERT_TZ(CONCAT(DATE(CONVERT_TZ(NOW(),'+00:00','+09:00')),' 00:00:00'),'+09:00','+00:00'));

数日前、数か月前

INSERT INTO `table_name` (colume_name) VALUES (CONVERT_TZ(CONCAT(DATE(DATE_SUB(CONVERT_TZ(NOW(),'+00:00','+09:00'),INTERVAL 1 DAY)),' 00:00:00'),'+09:00','+00:00'));

##No.3
要望から知って、指定時刻は00:00:00である。それに、CURRENT_DATE()とNOW()をさらに詳しく理解されている場合、No.1をこのような形に変更できる。

当日

INSERT INTO `table_name` (colume_name) VALUES (TIMESTAMPADD(HOUR,-9,DATE(TIMESTAMPADD(HOUR,9,CURRENT_DATE()))));

数日前、数か月前

INSERT INTO `table_name` (colume_name) VALUES (TIMESTAMPADD(HOUR,-9,DATE(DATE_SUB(TIMESTAMPADD(HOUR,9,CURRENT_DATE()),INTERVAL 1 DAY))));

#Conclusions
独特な案件要望ではないかと思われているかもしれないが、上記いずれの方法でテストデータを作成すると、いつになっても、データを一々直さなく、指定した日付のものをINSERTできると考えられた。

#Appendices
1. MySQL 5.7 Reference Manual / Functions and Operators / Date and Time Functions
2. 世界のタイムゾーンと主要都市の時差を計算