シェルからSQL応用(01_翌月最終日をYYYYMMDD形式で取得,MAC,zsh)
概要
SQLをシェルから実行することの実戦的な利用方法として各ソフトのSQLで使用可能な関数の機能を使った計算とか文字列処理の結果の取得がある。ログファイルの中身やファイル名自身が日付を含んでいる場合はとても多いからである。
年月日の計算はプログラム的にとか、表計算で対応しようとすると、結構複雑なことになる場合があるように思われるが、RDB環境のSQLでは暦の計算に役立つ関数が装備されている場合が多い。（ただし含まれない環境もある）
例として、翌月の最終日を数字８桁（YYYYMMDD)の形式で取り出してみる。
取出す方法は使用ソフトの装備機能に対応したいくつかの方法が考えられる。
月単位で加減する機能で翌月を算出後、月末日を表示する機能を組合せを組み合わせた方法
月単位で加減する機能で翌々月を算出後、日単位で加減する機能で翌々月開始日の前日を表示する方法
月単位や日単位での加減する機能がない場合は各月に対応したロジックを作成して算出する方法
下記に各ソフトの実装に合わせた上記方法に対応した方法を略述する。
実行例
方法１
HSQLDBにはlast_dayという月末日算出用関数が装備されている。
翌月をdateadd(month,1,current_date)で算出したあと、last_dayで月末日を算出し、TO_CHARでのフォーマット変換により値を得ることができる。
-zsh % java -jar ${HSQLDB_HOME}/lib/sqltool.jar --inlineRc=url=jdbc:hsqldb:mem:. --sql="values(TO_CHAR(last_day(dateadd(month,1,current_date)),'YYYYMMdd'));"
20230430方法２
HSQLDBでの別解として
DATEADD(month,2,current_date)で翌々月を算出後、TRUNCで月単位の切捨て（実際のところは月開始日算出）を行い、DATEADD(day,-1,)でその前日を算出する方法もある。
-zsh % java -jar ${HSQLDB_HOME}/lib/sqltool.jar --inlineRc=url=jdbc:hsqldb:mem:. --sql="values(TO_CHAR(DATEADD(day,-1,TRUNC(DATEADD(month,2,current_date),'MONTH')),'YYYYMMdd');"
20230430H2database,sqlite3,csvqもほぼ同様の方法で算出が行える。
尚、関数は各ソフトのそれぞれの拡張機能を利用しているため、全く互換性がない実装になってしまう。
H2DATABASEでの実装例
-zsh % java -cp ${H2DRIVERS}/h2-2.1.214.jar org.h2.tools.Shell -url jdbc:h2:mem:. -sql "select formatdatetime(dateadd(day,-1,formatdatetime(dateadd(month,2,current_date),'YYYY-MM-01')),'YYYYMMdd') as YYYYMMDD;"
YYYYMMDD
20230430
(1 row, 180 ms)sqlite3での実装例
-zsh % echo "select strftime('%Y%m%d',date('now','start of month','+2 month','-1 day'));" | sqlite3
20230430csvqでの実装例
-zsh % csvq -f csv -N "select datetime_format(add_day(year(add_month(now(),2))||'-'||month(add_month(now(),2))||'-01',-1),'%Y%m%d');"
20230430方法３
Apache Derbyの場合、Built-in関数に月や日を加減する関数が含まれていない。
そのため、各月に対応したロジックを自分で組み込んで実装する必要がある。
下記ではSQLのCASE WHEN機能で条件分けするとともに、2月の末日については閏年計算ロジックを実装して対応している。
-zsh % echo "select SUBSTR(CHAR(YEAR(CURRENT_DATE)+MONTH(CURRENT_DATE)/12),1,4)|| CASE WHEN MONTH(CURRENT_DATE) IN (3,5,8,12) THEN '0'||SUBSTR(CHAR(MOD(MONTH(CURRENT_DATE)+1,12)),1,1)||'30' WHEN MONTH(CURRENT_DATE) = 10 THEN '1130' WHEN MONTH(CURRENT_DATE) IN (2,4,6,7) THEN '0'||SUBSTR(CHAR(MOD(MONTH(CURRENT_DATE)+1,12)),1,1)||'31' WHEN MONTH(CURRENT_DATE) IN (9,11) THEN SUBSTR(CHAR(MOD(MONTH(CURRENT_DATE)+1,12)),1,2)||'31' ELSE CASE WHEN MOD(YEAR(CURRENT_DATE),400) = 0 THEN '0229' ELSE CASE WHEN MOD(YEAR(CURRENT_DATE),100) = 0 THEN '0228' ELSE CASE WHEN MOD(YEAR(CURRENT_DATE),4) = 0 THEN '0229' ELSE '0228' END END END END FROM SYSIBM.SYSDUMMY1;" | java -p ${DERBY_HOME}/lib -Dij.connection..="jdbc:derby:memory:.;create=true" -m org.apache.derby.tools/org.apache.derby.tools.ij
ijバージョン10.16
.* - jdbc:derby:memory:.
*=現行接続
ij> 1
--------
20230430
1行が選択されました
ij> % 環境
実行日
-zsh % date
2023年 3月12日 日曜日 15時58分00秒 JSTプロンプト設定
% PS1="%N %# "各種版数
-zsh % java -jar ${HSQLDB_HOME}/lib/sqltool.jar --inlineRc=url=jdbc:hsqldb:mem:. --sql="select DATABASE_VERSION() from (values(0));"
2.7.1
-zsh % java -cp ${H2DRIVERS}/h2-2.1.214.jar org.h2.tools.Shell -url jdbc:h2:mem:. -sql "values(h2version());"
C1
2.1.214
(1 row, 103 ms)
-zsh % echo -n "VALUES SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('DataDictionaryVersion');" | java -p ${DERBY_HOME}/lib -Dij.connection..="jdbc:derby:memory:.;create=true" -m org.apache.derby.tools/org.apache.derby.tools.ij
ijバージョン10.16
.* - jdbc:derby:memory:.
*=現行接続
ij> 1
--------------------------------------------------------------------------------------------------------------------------------
10.16
-zsh % which sqlite3
/usr/bin/sqlite3
-zsh % sqlite3 -version
3.39.5 2022-10-14 20:58:05 554764a6e721fab307c63a4f98cd958c8428a5d9d8edfde951858d6fd02daapl
-zsh % which csvq
/usr/local/bin/csvq
-zsh % csvq -v
csvq version 1.17.11
-zsh % java -version
openjdk version "19.0.2" 2023-01-17
OpenJDK Runtime Environment Homebrew (build 19.0.2)
OpenJDK 64-Bit Server VM Homebrew (build 19.0.2, mixed mode, sharing)
-zsh % sw_vers
ProductName: macOS
ProductVersion: 13.2.1
BuildVersion: 22D68参照
環境変数設定などは下記を参照
シェルからSQL実行する(hsqldb-01,MAC,zsh,Java19)
シェルからSQL実行する(Apache Derby-01,MAC,zsh,Java19)
シェルからSQL実行する(h2database-01,MAC,zsh,Java19)
シェルからSQL実行する(sqlite3-01,MAC,zsh)
シェルからSQL実行する(csvq-01,MAC,zsh)