[pgsql-jp: 29990] Re: 24:00を超えた場合の時刻表示方法について

[T.Suzuki]

鈴木と申します。

既に石田さんが、大変参考になるSQLを紹介されている上、
ご本人が解決されているので、邪魔でしかないかも知れませんが。。

> viewを使ってセレクトしてきた方が早いかな〜と思っていたのですが、
>
> >PostgreSQLで解決することにこだわらず、PHPなどのWEBアプリケーション
> >の表示側で解決してもいいじゃないでしょうか？
>
> phpのアプリ側で対応して、もう一度SQL文を勉強しなおします。

勤怠管理系の処理である場合、勤怠上の日付が変わるのは、
６時とか７時である為、その扱い(変換処理)にかなり苦労しました。

　・日付を超えて、「在場時間 = 退勤時間 - 出勤時間」をSQLで行いたい
　・「在場時間から休憩時間を減算」をSQLで行いたい

こういった処理を、（6時とか7時とか）基準時間をもとに計算したかったので、
時刻←→Int時間の変換を、PL/pgSQLで関数化してみました。

> phpのアプリ側で対応して　〜略
psqlから、ちょっとしたSQLを投げる際に、PHPのクラスでは使えなくて
思い切って作りました。

以下に、そのPL/ｐｇSQLを紹介させて下さい。
#サーバに掲載してリンクを張りたい所ですが、場所が無い為、
#メールにて紹介させて下さい。
----------------------------------------------------
/**
 * TO_INTERVAL()
 * 時刻形式の表現を引数に受け取り、基準時間からの経過分を返します。
 * ※プロシージャ作成時に基準時刻(root_time)を変更しておく必要があります。
 *
 * @language     plpgsql
 * @date         2001/11/14
 * @author       t_suzuki
 * @param        text     ":"区切りの時刻表現された文字列
 * @return       integer  基準時間からの経過時間を分単位で表した数値
 */
CREATE or REPLACE FUNCTION to_interval(text)
RETURNS integer AS '
Declare
    ins_text alias for $1;                          --引数の別名定義
    root_time constant integer not null default 7;  --開始基準時刻
    center          integer;    --引数内の文字列で、「：」の位置
    total_time      integer;    --合計値（分）【戻り値】
    houre_time      integer;    --時間単位部分
    minute_time     integer;    --分単位部分
Begin
    --引数の文字列で「：」の位置を調べる
    center := position('':'' in ins_text);
    IF center < 1 THEN          --「：」が無い
        RETURN NULL;            --「：」が無い場合は、NULL値を返す
    END IF;
    IF character_length(ins_text) <= center+1 THEN
      --分が1桁以下の入力のため、適切な値を入れる事が出来ない為、NULLを返す
        RETURN NULL;
    END IF;
    IF center = 1 THEN             --１文字目が「：」であるとき
        --開始基準時刻が省略されていると判断し、補完する
        houre_time := root_time;
    ELSE
        --時間部分を取り出す
        houre_time := substring( ins_text from 0 for center)::int;
    END IF;
    --分の部分を取り出す
    minute_time := substring( ins_text from center+1 for 5)::int;
    --24時間表記で、0〜基準時刻のときは24を足す
    IF houre_time < root_time THEN
        houre_time := houre_time + 24;
    ELSE
        --24時間表記で、24+基準時刻の入力までは許可
        IF houre_time - root_time >= 24 THEN
            --範囲外の入力は、正式な解釈が出来ないため無効
            RETURN NULL;
        END IF;
    END IF;
    --開始時間を基点として時間に変更
    houre_time := houre_time - root_time;
    houre_time := houre_time * 60;           --時間単位を分に変換
    total_time := houre_time + minute_time;  --基点時間を計算する
    RETURN total_time;
End;
' LANGUAGE 'plpgsql';

/*-----------------------------------------------------*/
/**
 * TO_CLOCK()
 * 基準時間からの経過分を引数に受け取り、時刻形式の表現を返します。
 * 経過分の有効範囲は、 0〜1439、-1〜-1439 とします。
 * 有効範囲外の数値が引数に与えられた場合、NULLを返します。
 * この為、集計関数中で使用した場合、有効範囲外の数値の影響で集計
 *結果がNULLになる様に考慮してあります。
 * ※プロシージャ作成時に基準時刻(root_time)を変更しておく必要があります。
 *
 * @language     plpgsql
 * @date         2001/11/14
 * @author       t_suzuki
 * @param        integer  基準時間からの経過時間を分単位で表した数値
 * @return       text     ":"区切りの時刻表現された文字列
 */
CREATE or REPLACE FUNCTION to_clock(int)
RETURNS text AS '
Declare
    ins_minute alias for $1;    --引数の別名
    root_time CONSTANT integer NOT NULL DEFAULT 7;    --基準時刻
    clean_minute    integer;    --範囲チェック完了後の値
    hour_time       text;       --時間単位の表示部分
    minute_time     text;       --分単位の表示部分
    clock_text      text;       --戻り値
Begin
    /**
     * 入力範囲チェック
     *   有効範囲 0〜1439/-1〜-1439 （前後基準時間を考慮）
     *   引数がマイナスの場合、基準時刻からの逆経過時間とする
     */
    IF ins_minute >= 0 AND ins_minute < 1440 + root_time * 60 THEN
        --正の有効値をセット
        clean_minute := ins_minute;
    ELSE
        IF ins_minute < 0 AND ins_minute > -1440 - root_time * 60 THEN
            --負の有効値をセット
            clean_minute := ins_minute + 1440;
        ELSE
            --有効範囲外 NULL値を返す
            RETURN NULL;
        END IF;
    END IF;
    /**
     * 整形処理
     * "24:00"を"0:00"と出力する場合、※1の条件をコメントアウトして
     * ※2を使用する。
     */
    --時刻を計算し、TEXT 型に整形する
    --※1  24時以上をそのまま表示する条件
    IF clean_minute >= 1440 THEN
    --※2  24時以上を繰り下げる条件
--  IF clean_minute >= 1440 - root_time * 60 THEN
        hour_time := to_char(clean_minute / 60 - 24 + root_time, ''00'');
    ELSE
        hour_time := to_char(clean_minute / 60 + root_time, ''00'');
    END IF;
    --分を計算し、TEXT 型に整形する
    minute_time    := to_char(clean_minute % 60 , ''00'');
    --文字列の結合
    hour_time  := ltrim(hour_time);
    minute_time := ltrim(minute_time);
    clock_text  := hour_time || '':'' || minute_time;
    RETURN clock_text;
End;
' LANGUAGE 'plpgsql';

----------------------------------------------------
■使用方法
・7：00（基準時刻）からの経過分を返します。
# select to_interval('9:00');
 to_interval
----------
           120

# select to_clock(120);
 to_clock
--------
 09:00

日付を超えた、「在場時間 = 退勤時間 - 出勤時間」は、
以下のように問い合わせ可能です。
select to_clock(to_interval(out_time) - to_interval(in_time)) from jobday;

#目的指向が強く、汎用性はないものですが…。
 -----------------------------------------
      鈴木 徹 (SUZUKI Toru)
      KENWOOD ENGINEERING CORPORATION
      E-mail:t_suzuki ＠ kenwood-eng.co.jp
 -----------------------------------------