ありがとう。また会おう。

まぁゆるりとやっていきますよと。

「足し算の順序」アナタは大丈夫?

この記事は MySQL Casual Advent Calendar 2015 の20日目です。

1ヶ月ほど前ですが、こんな記事がネット上で話題になりましたね。
r25.yahoo.co.jp
togetter.com

個人的には、算数(数学)と国語の授業を取り違えてるんじゃないかな・・・と思ってもやもやします。
数学は抽象化して扱うから、様々な定理・公式が使えるというのに
小学生どあたまの教育でこんな風に教えるんじゃ、ますます算数・数学嫌いを増やすんじゃないかと危惧したりするのですが・・・


ところで。
MySQL(に限った話ではないのですが)では、この「足し算の順序」が重要になる場合があります。

AWS上のMySQLであるRDSでは、タイムゾーンの設定がデフォルトでUTCなので、NOW() 関数を使うと 日本の標準時間(JST)とは9時間ずれた日時が出力されます

※サーバシステム時刻を確認

$ date
Sun Dec 20 12:01:54 JST 2015

※RDSに接続し、NOW()関数を出力

$ mysql -u norii -p -h norii-db.************.ap-northeast-1.rds.amazonaws.com
mysql> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2015-12-20 03:02:19 |
+---------------------+
1 row in set (0.01 sec)

なので、これを補正するには、9時間ずらす必要があります*1

mysql> SELECT NOW() + INTERVAL 9 HOUR;
+-------------------------+
| NOW() + INTERVAL 9 HOUR |
+-------------------------+
| 2015-12-20 12:02:29     |
+-------------------------+
1 row in set (0.00 sec)

時に、DBにあるデータに対し、「1ヶ月前」の日時が必要になることがあります。
素朴に現在日時に対する「1ヶ月前」を、上記の要領で求めると

mysql> SELECT NOW() + INTERVAL 9 HOUR - INTERVAL 1 MONTH;
+--------------------------------------------+
| NOW() + INTERVAL 9 HOUR - INTERVAL 1 MONTH |
+--------------------------------------------+
| 2015-11-20 12:02:41                        |
+--------------------------------------------+
1 row in set (0.00 sec)

となります。ここで、「+ INTERVAL 9 HOUR」と「- INTERVAL 1 MONTH」の順序を入れ替えてみます。

mysql> SELECT NOW() - INTERVAL 1 MONTH + INTERVAL 9 HOUR;
+--------------------------------------------+
| NOW() - INTERVAL 1 MONTH + INTERVAL 9 HOUR |
+--------------------------------------------+
| 2015-11-20 12:02:48                        |
+--------------------------------------------+
1 row in set (0.00 sec)

NOW() 関数通してるので秒は実行のタイムラグの分ずれてますが、基本的には同じ日時を指し示しています。

ですが、この計算式は、実行する日時によっては、等値になりません。

たとえば、このSQLを 日本時間の 12月1日 1:00 (UTC では 11月30日 16:00)に実行したとしましょう。
(以下ではNOW()関数ではなく、日時をリテラルで渡しています)

mysql> SELECT '2015-11-30 16:00:00' + INTERVAL 9 HOUR - INTERVAL 1 MONTH;
+------------------------------------------------------------+
| '2015-11-30 16:00:00' + INTERVAL 9 HOUR - INTERVAL 1 MONTH |
+------------------------------------------------------------+
| 2015-11-01 01:00:00                                        |
+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT '2015-11-30 16:00:00' - INTERVAL 1 MONTH + INTERVAL 9 HOUR;
+------------------------------------------------------------+
| '2015-11-30 16:00:00' - INTERVAL 1 MONTH + INTERVAL 9 HOUR |
+------------------------------------------------------------+
| 2015-10-31 01:00:00                                        |
+------------------------------------------------------------+
1 row in set (0.00 sec)

と、なんと1日ずれてしまいました。

まぁ考えてみれば当たり前の話で
1ヶ月の日数は、月によって異なるので
上記のSQLで、先に9時間足すと、日付またいで12月1日なるので、その1ヶ月前で11月1日になる
先に1ヶ月減算すると、10月30日になって、そこに9時間足すことになりますが、10月はもう1日ありますから、10月31日になると。

Webアプリケーションで集計組む場合は、プログラム側から日時を渡せばいいですが
ストアドプロシージャでこのような日付周りを扱う場合は、地味にハマるかもしれません。

・・・というか、地味にハマったんでこのエントリ書いたんですけどね(´・ω・`)
なんで素直な順番で書けばなんにも問題ないのにわざわざ順番変えちゃったんだろう。。。

*1:Webアプリケーションから扱う場合は、MySQLのNOW()関数などは使わず、アプリケーション側で日時リテラルを組み立てて渡すべきです