DockerでMySQL起動して日付変えてSQL実行

MySQL 起動

docker run -d \
  --name mysql-test \
  --privileged \
  -e TZ=UTC \
  -e MYSQL_ROOT_PASSWORD=root \
  --name mysql-test \
  mysql:latest

時間を変更するために Bash で入る

docker exec -it mysql-test bash

date -s コマンドで時間を変更して exit

bash-5.1# date
Wed Oct 23 05:48:51 UTC 2024

bash-5.1# date -s "2024-10-23 22:30:00"
Wed Oct 23 22:30:00 UTC 2024

bash-5.1# date
Wed Oct 23 22:30:01 UTC 2024

bash-5.1# exit

mysql に接続する

docker exec -it mysql-test mysql -uroot -proot

以下の SQL を実行

SELECT
  NOW() AS 'NOW',
  NOW() - INTERVAL 3 DAY AS '-3 DAY NOW',
  NOW() - INTERVAL 72 HOUR AS '-72 HOUR NOW',
  CURDATE() AS 'CURDATE',
  CURDATE() - INTERVAL 3 DAY AS '-3 DAY CURDATE',
  CURDATE() - INTERVAL 72 HOUR AS '-72 HOUR CURDATE',
  CONVERT_TZ(NOW(), 'UTC', 'Asia/Tokyo') AS 'JST CURDATE', 
  CONVERT_TZ(NOW(), 'UTC', 'Asia/Tokyo') - INTERVAL 3 DAY AS '-3 DAY in JST CURDATE',
  CONVERT_TZ(NOW(), 'UTC', 'Asia/Tokyo') - INTERVAL 72 HOUR AS '-72 HOUR in JST CURDATE'\G

実行結果を確認して exit

mysql> SELECT
    ->   NOW() AS 'NOW',
    ->   NOW() - INTERVAL 3 DAY AS '-3 DAY NOW',
    ->   NOW() - INTERVAL 72 HOUR AS '-72 HOUR NOW',
    ->   CURDATE() AS 'CURDATE',
    ->   CURDATE() - INTERVAL 3 DAY AS '-3 DAY CURDATE',
    ->   CURDATE() - INTERVAL 72 HOUR AS '-72 HOUR CURDATE',
    ->   CONVERT_TZ(NOW(), 'UTC', 'Asia/Tokyo') AS 'JST CURDATE',
    ->   CONVERT_TZ(NOW(), 'UTC', 'Asia/Tokyo') - INTERVAL 3 DAY AS '-3 DAY in JST CURDATE',
    ->   CONVERT_TZ(NOW(), 'UTC', 'Asia/Tokyo') - INTERVAL 72 HOUR AS '-72 HOUR in JST CURDATE'\G
*************************** 1. row ***************************
                    NOW: 2024-10-23 22:30:14
             -3 DAY NOW: 2024-10-20 22:30:14
           -72 HOUR NOW: 2024-10-20 22:30:14
                CURDATE: 2024-10-23
         -3 DAY CURDATE: 2024-10-20
       -72 HOUR CURDATE: 2024-10-20 00:00:00
            JST CURDATE: 2024-10-24 07:30:14
  -3 DAY in JST CURDATE: 2024-10-21 07:30:14
-72 HOUR in JST CURDATE: 2024-10-21 07:30:14
1 row in set (0.00 sec)

mysql> exit

後始末（Docker を強制終了してコンテナも削除）

docker rm -f mysql-test