今回の記事は、SQL-Server 2008 Expressで、時間の計算を便利にしてみようという特集の第一回目です。
時間の計算を電卓感覚で便利にしようというのは、以前、Windows Power Shellでやってみた事なのですが、あのノリをSQL-Server 2008にも持ち込めないかなと考えました。
しかし、Windows Power Shellと違って、SQL-Server 2008には、TimeSpan型に該当する型が存在していないため、C#言語でプログラムしたところで、直接は結果として返せないのです。
そこで、ちょっと悩んだのですが今回はこうしました。
まずは勤怠の計算とかに使うのが主目的と考え、時間:分の文字列をパラメータとして渡したら、結果はTimeSpanにこだわらず、シンプルに分に換算した数値で返してしまう関数をSQLCLR作ろうかなと。
たとえば、文字列で"01:00"と入力すれば、これは一時間だと判断して60と返す関数を作る。
"01:00" + "08:15"みたいな、時間:分文字列での計算も、分に換算しているから可能になるなぁと。
結果も分に換算した数値で出てくるけど、これは後々変換します。
あと、同じ時間を10回とか繰り返すのに備えて、繰り返し計算の回数パラメータも用意したんですが、結果は分に換算して返されるわけですから、単純に*10とか*20とか計算すれば良いだけだから、特に必要はなかったかも。コードの練習として今回はこのまま行きます。
SQLCLRの登録などについては、過去の記事をご参考になさって下さい。
■実行例■
SELECT dbo.SQLCLR_T('48:30',1) AS [48:30の結果];
SELECT dbo.SQLCLR_T('00:120',1) AS [00:120の結果];
SELECT dbo.SQLCLR_T('08:00',10) AS [08:00を10回の結果];
SELECT
dbo.SQLCLR_T('01:00',1) +
dbo.SQLCLR_T('00:15',10) AS [01:00 + 00:15を10回の計算結果]
■コード■
(インデントは消えております)
(無償版のVisual C# 2008でプログラムを書けます)
(全角の＜と＞は、半角に読み替えて下さい)
//*******************************************************************************
// * ローカル関数
//*******************************************************************************
//------------------------------------
//TimeSpan用の日時分配列作成
//このローカル関数は今後も使い回します
//------------------------------------
private static Int32[] CreateTimeSpanSource(
Int32 p_day,
Int32 p_hour,
Int32 p_minute
)
{
//結果格納エリア
Int32[] intArr_result = new Int32[3];
try
{
//値の格納
intArr_result[0] = p_day; //日
intArr_result[1] = p_hour; //時
intArr_result[2] = p_minute; //分
//分の時間への繰上げ計算
intArr_result[1] = intArr_result[1]+
(Int32)Math.Floor((Double)intArr_result[2]/(Double)60);
//分の調整計算
intArr_result[2] = intArr_result[2] -
(Int32)Math.Floor((Double)intArr_result[2]/(Double)60)*60;
//時間の日への繰上げ計算
intArr_result[0] = intArr_result[0] +
(Int32)Math.Floor((Double)intArr_result[1] / (Double)24);
//時間の調整計算
intArr_result[1] = intArr_result[1] -
(Int32)Math.Floor((Double)intArr_result[1] / (Double)24) * 24;
}
catch (System.Exception obj_err)
{
throw obj_err;
}
return intArr_result;
}
//*******************************************************************************
//* SQL関数
//*******************************************************************************
//------------------------------------
//日.時間:分文字列を分に変換する関数
//------------------------------------
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlInt32 SQLCLR_T(
SqlString p_value,
Int32 p_magnification
)
{
//入力値作業エリア
string str_value;
//結果格納エリア
Int32 int_result = 0;
//インデックス
Int32 i;
//チェック用書式1
Regex obj_rx1 = new Regex(@"^\d{1,4}\.\d{1,4}:\d{1,4}$");
//チェック用書式2
Regex obj_rx2 = new Regex(@"^\d{1,4}:\d{1,4}$");
//チェック用書式3
Regex obj_rx3 = new Regex(@"^\d+$");
//時間基礎エリア
TimeSpan obj_base;
//時間結果エリア
TimeSpan obj_result = new TimeSpan(0, 0, 0, 0);
//日時分文字列分解用キャラクタ配列
char[] charArr_c = new char[2]{'.',':'};
//日時分文字列格納配列
string[] strArr_ts = new string[3];
//日時分計算配列
Int32[] intArr_ts;
try
{
str_value = (string)p_value;
//書式チェック
if (!(obj_rx1.IsMatch(str_value)))
{
if (obj_rx2.IsMatch(str_value))
{ //書式2にマッチした場合は、日要素を補完して先に進む。
str_value = "0." + str_value;
}
else if (obj_rx3.IsMatch(str_value))
{ //書式3にマッチした場合は、日.時間要素を補完して先に進む。
str_value = "0.0:" + str_value;
}
else
{ //全書式アンマッチならエラー
throw new System.Exception("書式エラー。日(最大4桁).時間(最大4桁):分(最大4桁)でお願いします。");
}
}
//入力値を日時分配列に分解して格納
strArr_ts = str_value.Split(charArr_c);
//日時分配列の内容を自作関数CreateTimeSpanSource()で調整計算し、計算配列にセット
intArr_ts = CreateTimeSpanSource(
Int32.Parse(strArr_ts[0]),
Int32.Parse(strArr_ts[1]),
Int32.Parse(strArr_ts[2])
);
//時間基礎エリアにセット
obj_base = new TimeSpan(
intArr_ts[0],
intArr_ts[1],
intArr_ts[2],
0
);
//時間結果エリアにセット(倍率計算機能付き)
//@@@@@@@@@@[LOOP-START]@@@@@@@@@@
for (i = 0; i ＜ (Int32)p_magnification; i++)
{
obj_result = obj_result.Add(obj_base);
}
//@@@@@@@@@@[LOOP-END ]@@@@@@@@@@
//結果を分に換算して取得
int_result = (Int32)obj_result.TotalMinutes;
}
catch (System.Exception obj_err)
{
throw obj_err;
}
return (SqlInt32)int_result;
}
■SQL-Server関数■
(環境はご自分のものに読み替えお願いいたします。)
-- *******************************************************
-- SQLCLRの関数インターフェース
-- *******************************************************
-- ----------------------------------------------
-- 時間文字列を分に換算する
-- (パラメータ説明)
-- @p_value :"日.時間:分"
-- もしくは、"時間:分。"
-- ただし、各要素は数値最大4桁
-- @p_magnification:繰り返し計算数
-- ----------------------------------------------
create function SQLCLR_T(
@p_value nvarchar(14),
@p_magnification int
)
RETURNS int
AS EXTERNAL NAME ASSEMBLY_SQLCLR.CLS_SQLCLR.SQLCLR_T;
GO