SQLで日付カラムを連結、1年後、月末を出すなど #SQL

開始日と最終契約終了日などをテーブルに入れただけで年度ごとの更新日の数十日前から通知されたい的な需要があったのですが自動で更新したかった年度ごとの更新日を求めるSQLの部分の備忘録。
タイミングが、日付が過去になった直後に更新される前提。

mysql> select concat(DATE_FORMAT(DATE_ADD(t13_c5,INTERVAL 1 YEAR), '%Y-'), DATE_FORMAT(LAST_DAY(t13_c3), '%m-%d')) from testdb.table1 where t13_c1=80027xxx;
+------------------------------------------------------------------------------------------------------+
| concat(DATE_FORMAT(DATE_ADD(t13_c5,INTERVAL 1 YEAR), '%Y-'), DATE_FORMAT(LAST_DAY(t13_c3), '%m-%d')) |
+------------------------------------------------------------------------------------------------------+
| 2019-05-31                                                                                           | 
+------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
mysql> select * from testdb.table1 where t13_c1=80027xxx\G
*************************** 1. row ***************************
         t13_id: 31
      t13_valid: 1
   t13_reg_time: 1552648117
   t13_mod_time: NULL
t13_reg_user_id: 6
t13_mod_user_id: NULL
         t13_c1: 80027xxx
         t13_c2: 
         t13_c3: 2016-05-24
         t13_c4: 2021-05-31
         t13_c5: 2018-03-31　#ここを出した値にしたいだけ

DATE_FORMATは年だけ、または月日だけ出すのを定義、
DATE_ADDでカラム値の1年後をだす、
LAST_DAYでカラム値の月末をだす
concatで文字列連結

過去を出したいときはDATE_SUBみたいだけど今回特に使わなかった。
日付はいってるカラムの型はDATE型。
うっかりintでunixtimeとか入ってるときはcast(t3_c3 as date)で型変換かFROM_UNIXTIME関数でもフォーマット指定できるようです。

unixtimeをdateコマンドでDATE型っぽく変換するには以下のとおり。
date +%Y-%m-%d --date "1552648117"

shellスクリプト上で分岐処理で差分を求めたくてわざわざunixtimeで出したいときは、UNIX_TIMESTAMPという関数で出せました。

select t13_c1,UNIX_TIMESTAMP(t13_c4),UNIX_TIMESTAMP(t13_c5) from ${dbname}.${tblname} where t13_c4 > now() and t13_c5 < now();

select UNIX_TIMESTAMP(now());で出るのをdateで出したいときに。
date +%s
特定の日付をunixtimeにしたいならdate --date '2012/12/1' +%sなど。

通知対象の抽出は残り日のdatediffのbetweenで最初に0いれると過去の日付でない。

select t13_c1 as enrolle_num,t13_c2 as company,t13_c5 as current_end,t13_c4 as final_end,datediff(t13_c5,now()) 'valid days' from ${dbname}.${tblname} where datediff(t13_c5,now()) between 0 and 60;

やりたい内容を適当な文字列でググるとだいたい関数とか用例とか出てくるの楽だし実行してみてその通り動くのは楽しいですね。

今回時刻の型は扱わなかったけどカラムのDEFAULTなどの指定をCURRENT_TIMESTAMPとかにできるのは時刻の型なんだなーというのはなんとなく見かけました。
やってみた記事も個人的にはとっかかりにわかりやすくて好きですが、マニュアルみたほうが情報量がだいぶ多いかなとも思いました。

参考
https://hit.hateblo.jp/entry/mysql/concat-pipe
http://nowork.jugem.jp/?eid=12
https://qiita.com/sakura1116/items/3fef2ca5b5280eae22e8
http://mysql.javarou.com/dat/000849.html
https://www.dbonline.jp/mysql/type/index4.html
https://qiita.com/ykawakami/items/2449a24e3b82ff0cbab6
https://dev.mysql.com/doc/refman/5.6/ja/timestamp-initialization.html
https://dev.mysql.com/doc/refman/5.6/ja/date-and-time-functions.html