今回の記事では、SQL-Server 2008 (Express)において、時間の計算を自由にできる汎用的な関数を作ってみたいと思います。
時間の計算を便利にするという記事ならば、すでに以前の記事で書かせていただいておりますが、ちょっと汎用性が低かったのが気になっていました。
今回も、やはりキーパーソンはTime Span型。
これを、SQL-Server 2008から、便利な形で使おうというのが基本方針となります。
たとえば、"08:15"という時間文字列同士の計算も、10.5という数値同士の計算も、混在した計算も、一つの関数で可能とします。
得られる結果も、時間文字列と数値のどちらでも好きな方でOK。
というわけで、作ってみましたのが、SQLCLR_TS()という関数です。
パラメータがミソで、入力の単位と出力の形式を、TimeSpan型に合わせて指定可能としました。
10.5時間を分に換算したいという場合は
SQLCLR_TS(10.5,'FromHours','TotalMinutes');
とすれば良いようにしてみました。
"08:15"という文字を分に換算したいという場合は
SQLCLR_TS('08:15','','TotalMinutes');
とすれば良いようにしてみました。
時間の計算をする時は、一旦、出力の形式を、分(TotalMinutes)や秒(TotalSeconds)にしておいてから実行し、最後にその結果を好きな形に変換するというのが基本的な使用方法になりますね。
結果は、出力形式に応じて、数値だったり文字列だったり変わりますね。
これは、SQL-Serverが備えるsql_variant型という「なんでも型」を使う事で実現してみました。
SQLCLRの登録などにつきましては過去の記事を参考になさって下さい。
■実行例■
SELECT dbo.SQLCLR_TS('08:15','','TotalDays') as [08:15を日に換算];
SELECT dbo.SQLCLR_TS('08:15','','TotalHours') as [08:15を時間に換算];
SELECT dbo.SQLCLR_TS('08:15','','TotalMinutes') as [08:15を分に換算];
SELECT dbo.SQLCLR_TS(128.5,'FromDays','TIME') as [128.5(日)を、時間:分文字列に変換];
SELECT dbo.SQLCLR_TS(128.5,'FromHours','TIME') as [128.5(時間)を、時間:分文字列に変換];
SELECT dbo.SQLCLR_TS(128.5,'FromMinutes','TotalSeconds') as [128.5(分)を、秒に換算];
SELECT dbo.SQLCLR_TS(128.5,'FromSeconds','TotalMilliSeconds') as [128.5(秒)をミリ秒に換算];
--時:分形式の文字列で計算を行い、結果も時間:文字列で出す場合の例
--08:15が20回、0:30が10回、2:00が8回、0:15が1回を計算する。(計算は分に換算して行う)
SELECT
dbo.SQLCLR_TS(
Cast(dbo.SQLCLR_TS('08:15','','TotalSeconds') as float) * 20 +
Cast(dbo.SQLCLR_TS('0:30','','TotalSeconds') as float) * 10 +
Cast(dbo.SQLCLR_TS('2:00','','TotalSeconds') as float) * 8 +
Cast(dbo.SQLCLR_TS('0:15','','TotalSeconds') as float)
,'FromSeconds','TIME') AS [計算結果];
■コード■
(無償版のVisual C# 2008でプログラムを書けます)
(インデントは消えています)
(全角の＜＞は半角に読み替えて下さい)
//*******************************************************************************
// * ローカル関数
//*******************************************************************************
//------------------------------------
//TimeSpan用の日時分配列作成
//前回のSQLCLR_T()の時と同じ関数です。
//------------------------------------
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 object SQLCLR_TS(
SqlString p_value,
SqlString p_in_param,
SqlString p_out_param
)
{
//入力パラメータ受け取りエリア
string str_value;
//結果格納エリア
object V_result = null;
//書式チェック用1
Regex obj_rx1 = new Regex(@"^\d+\.\d+:\d+$");
//書式チェック用2
Regex obj_rx2 = new Regex(@"^\d+:\d+$");
//書式チェック用3
Regex obj_rx3 = new Regex(@"^(-)?\d+(\.\d+)?$");
//日時分文字列分解キャラクタ配列
char[] charArr_c = new char[] { '.', ':' };
//日時分文字列格納配列
string[] strArr_ts = new string[3];
//日時分計算配列
Int32[] intArr_ts = new Int32[3];
//時間コア
TimeSpan obj_core;
//インデックス
Int32 i;
try
{
str_value = (string)p_value;
//書式チェック
if (!(obj_rx3.IsMatch(str_value)))
{ //時間文字列が来た場合
if (obj_rx2.IsMatch(str_value))
{ //日の要素を補完
str_value = "0." + str_value;
}
else if (!(obj_rx1.IsMatch(str_value)))
{ //エラー
throw new System.Exception("値は数値または'日.時間:日'か'時間:日'形式でなければなりません。");
}
//日時分配列格納
strArr_ts = str_value.Split(charArr_c);
//日時分配列セット
//@@@@@@@@@@[LOOP-START]@@@@@@@@@@
for (i = 0; i ＜ strArr_ts.Length; i++)
{
intArr_ts[i] = Int32.Parse(strArr_ts[i]);
}
//@@@@@@@@@@[LOOP-END ]@@@@@@@@@@
//日時の調整計算。自作関数CreateTimeSpanSource()による
intArr_ts = CreateTimeSpanSource(intArr_ts[0], intArr_ts[1], intArr_ts[2]);
//コアの作成
obj_core = new TimeSpan(intArr_ts[0], intArr_ts[1], intArr_ts[2], 0);
}
else
{ //数字が来た場合
switch (((string)p_in_param).ToUpper())
{
case "FROMDAYS":
obj_core = TimeSpan.FromDays(Double.Parse(str_value));
break;
case "FROMHOURS":
obj_core = TimeSpan.FromHours(Double.Parse(str_value));
break;
case "FROMMINUTES":
obj_core = TimeSpan.FromMinutes(Double.Parse(str_value));
break;
case "FROMSECONDS":
obj_core = TimeSpan.FromSeconds(Double.Parse(str_value));
break;
case "FROMMILLISECONDS":
obj_core = TimeSpan.FromMilliseconds(Double.Parse(str_value));
break;
default:
throw new System.Exception("入力側の指示エラーです。FROMDAYS,FROMHOURS,FROMMINUTES,FROMSECONDS,FROMMILLISECONDSです。");
}
}
//出力側指示チェック
switch (((string)p_out_param).ToUpper())
{
case "TIME": //時間:分の文字列に変換
V_result = (obj_core.Days * 24 + obj_core.Hours).ToString() +
":" +
obj_core.Minutes.ToString("00");
break;
case "TIME2": //日.時(23時):分の文字列に変換
V_result = obj_core.Days.ToString() + "." +
obj_core.Hours.ToString("00") + ":" +
obj_core.Minutes.ToString("00");
break;
case "TIME3": //日.時(23時):分:秒.ミリ秒の文字列に変換
V_result = obj_core.Days.ToString() + "." +
obj_core.Hours.ToString("00") + ":" +
obj_core.Minutes.ToString("00") + ":" +
obj_core.Seconds.ToString("00") + "." +
obj_core.Milliseconds.ToString("00").Substring(0, 2);
break;
case "DAYS": //日.時(23時):分:秒.ミリ秒のうち「日」を切り出し
V_result = obj_core.Days;
break;
case "HOURS"://日.時(23時):分:秒.ミリ秒のうち「時」を切り出し
V_result = obj_core.Hours;
break;
case "MINUTES"://日.時(23時):分:秒.ミリ秒のうち「分」を切り出し
V_result = obj_core.Minutes;
break;
case "SECONDS"://日.時(23時):分:秒.ミリ秒のうち「秒」を切り出し
V_result = obj_core.Seconds;
break;
case "MILLISECONDS"://日.時(23時):分:秒.ミリ秒のうち「ミリ秒」を切り出し
V_result = obj_core.Milliseconds;
break;
case "TOTALDAYS": //全て日に換算
V_result = obj_core.TotalDays;
break;
case "TOTALHOURS": //全て時間に換算
V_result = obj_core.TotalHours;
break;
case "TOTALMINUTES": //全て分に換算
V_result = obj_core.TotalMinutes;
break;
case "TOTALSECONDS"://全て秒に換算
V_result = obj_core.TotalSeconds;
break;
case "TOTALMILLISECONDS"://全てミリ秒に換算
V_result = obj_core.TotalMilliseconds;
break;
default:
throw new System.Exception("出力側の指示エラーです。TIME(時間:分),TIME2(日.時:分),TIME3(日.時:分:秒.ミリ秒),DAYS,HOURS,MINUTES,SECONDS,MILLISECONDS,TOTALDAYS,TOTALHOURS,TOTALMINUTES,TOTALSECONDS,TOTALMILLISECONDSです。");
}
}
catch (System.Exception obj_err)
{ //例外が発生したら、SQL-Serverに投げつけて強制終了させる
throw obj_err;
}
return V_result;
}
■SQL-Server関数インターフェース■
(環境はご自分のものに読み替えお願いいたします)
-- *******************************************************
-- SQLCLRの関数インターフェース
-- *******************************************************
-- ----------------------------------------------
-- 時間を計算する関数
-- (パラメータ説明)
-- @p_value : "日.時間:分"
-- もしくは、"時間:分。"
-- もしくは、数値
-- @p_in_param : @p_valueに数値を入力した場合に有効
-- どの単位の入力かを指示。
-- @p_valueに時間文字列を入力した場合は無視
-- (選択できるパラメータの意味)
-- FromDays =@p_valueは日換算
-- FromHours=@p_valueは時間換算
-- FromMintes=@p_valueは分換算
-- FromSeconds=@p_valueは秒換算
-- FromMilliSeconds=@p_valueはミリ秒換算
-- @p_out_param : どの単位に変換して出力するかを指示
-- (選択できるパラメータの意味)
-- TIME=時間:分の文字列
-- TIME2=日.時間:分の文字列
-- Days=日を切り取る
-- Hours=時を切り取る
-- Minutes=分を切り取る
-- Seconds=秒を切り取る
-- MilliSeconds=ミリ秒を切り取る
-- TotalDays=日に換算
-- TotalHours=時間に換算
-- TotalMinutes=分に換算
-- TotalSeconds=秒に換算
-- TotalMilliSeconds=ミリ秒に換算
-- ----------------------------------------------
create function SQLCLR_TS(
@p_value nvarchar(max),
@p_in_param nvarchar(16),
@p_out_param nvarchar(17)
)
RETURNS sql_variant
AS EXTERNAL NAME ASSEMBLY_SQLCLR.CLS_SQLCLR.SQLCLR_TS;
GO
