MySQLにてSELECT時にdate型のカラムから干支(十二支)を計算し表示する方法

MySQLにdate型のカラムに誕生年月日が格納されており、そこから干支(十二支)を計算して取得したい。以下に対応方法をメモ。

テーブル構造とデータ内容

mysql> SHOW COLUMNS FROM `test_table`;
+----------+------+------+-----+---------+----------------+
| Field    | Type | Null | Key | Default | Extra          |
+----------+------+------+-----+---------+----------------+
| id       | int  | NO   | PRI | NULL    | auto_increment |
| birthday | date | NO   |     | NULL    |                |
+----------+------+------+-----+---------+----------------+

mysql> SELECT * FROM `test_table`;
+----+------------+
| id | birthday   |
+----+------------+
|  1 | 2020-01-01 |
|  2 | 2019-01-01 |
|  3 | 2018-01-01 |
|  4 | 2017-01-01 |
|  5 | 2016-01-01 |
|  6 | 2015-01-01 |
|  7 | 2014-01-01 |
|  8 | 2013-01-01 |
|  9 | 2012-01-01 |
| 10 | 2011-01-01 |
| 11 | 2010-01-01 |
| 12 | 2009-01-01 |
| 13 | 2008-01-01 |
+----+------------+

対応方法

SQL文

SELECT `birthday`, 
    CASE 
        WHEN (DATE_FORMAT(`birthday`,"%Y") - 4) % 12 = 0 THEN '子(ねずみ)' 
        WHEN (DATE_FORMAT(`birthday`,"%Y") - 4) % 12 = 1 THEN '丑(うし)' 
        WHEN (DATE_FORMAT(`birthday`,"%Y") - 4) % 12 = 2 THEN '寅(とら)' 
        WHEN (DATE_FORMAT(`birthday`,"%Y") - 4) % 12 = 3 THEN '卯(うさぎ)' 
        WHEN (DATE_FORMAT(`birthday`,"%Y") - 4) % 12 = 4 THEN '辰(たつ)' 
        WHEN (DATE_FORMAT(`birthday`,"%Y") - 4) % 12 = 5 THEN '巳(み)' 
        WHEN (DATE_FORMAT(`birthday`,"%Y") - 4) % 12 = 6 THEN '午(うま)' 
        WHEN (DATE_FORMAT(`birthday`,"%Y") - 4) % 12 = 7 THEN '未(ひつじ)' 
        WHEN (DATE_FORMAT(`birthday`,"%Y") - 4) % 12 = 8 THEN '申(さる)' 
        WHEN (DATE_FORMAT(`birthday`,"%Y") - 4) % 12 = 9 THEN '酉(とり)' 
        WHEN (DATE_FORMAT(`birthday`,"%Y") - 4) % 12 = 10 THEN '戌(いぬ)' 
        WHEN (DATE_FORMAT(`birthday`,"%Y") - 4) % 12 = 11 THEN '亥(い)' 
    END AS `eto`
FROM `test_table`;

出力結果

+------------+----------------+
| birthday   | eto            |
+------------+----------------+
| 2020-01-01 | 子(ねずみ)     |
| 2019-01-01 | 亥(い)         |
| 2018-01-01 | 戌(いぬ)       |
| 2017-01-01 | 酉(とり)       |
| 2016-01-01 | 申(さる)       |
| 2015-01-01 | 未(ひつじ)     |
| 2014-01-01 | 午(うま)       |
| 2013-01-01 | 巳(み)         |
| 2012-01-01 | 辰(たつ)       |
| 2011-01-01 | 卯(うさぎ)     |
| 2010-01-01 | 寅(とら)       |
| 2009-01-01 | 丑(うし)       |
| 2008-01-01 | 子(ねずみ)     |
+------------+----------------+

参考サイト

https://patareru.net/archives/129