テストデータを使って別のアプローチで勤務時間を計算してみよう。

まずは前回同様、「09:00～18:00」勤務で「12:00～13:00」休憩の場合。
┌──────────────────────────────────────┐
│WITH "test"("start",                                                        │
│            "end"  ) AS (                                                   │
│         VALUES('09:00'::time,                                              │
│                '18:00'::time)                                              │
│     )                                                                      │
│SELECT trunc((extract(EPOCH FROM '12:00'::time - "start"::time)             │
│            + extract(EPOCH FROM "end"         - '13:00'::time)) / 60)      │
│    FROM "test";                                                            │
└──────────────────────────────────────┘
続いて、さらに「18:00～18:30」の休憩をはさんで「18:30～21:30」が平常残業、
「21:30～22:00」の休憩をはさんで「22:00～24:00」が深夜残業の場合。
以下のように時間帯マスタを駆使して計算が可能だ。
┌──────────────────────────────────────┐
│WITH "t"("s",                                                               │
│         "e"  ) AS (                                                        │
│         VALUES('09:30'::time,                                              │
│                '22:30'::time)                                              │
│     ),                                                                     │
│     "r"("s1",                                                              │
│         "e1"  ,                                                            │
│         "s2",                                                              │
│         "e2",                                                              │
│         "s3",                                                              │
│         "e3"  ,                                                            │
│         "s4",                                                              │
│         "e4"  ) AS (                                                       │
│         VALUES('09:00'::time,                                              │
│                '12:00'::time,                                              │
│                '13:00'::time,                                              │
│                '18:00'::time,                                              │
│                '18:30'::time,                                              │
│                '21:30'::time,                                              │
│                '22:00'::time,                                              │
│                '23:00'::time)                                              │
│     )                                                                      │
│SELECT CASE WHEN "t1" > 0 THEN "t1" ELSE 0 END AS "平常",                   │
│       CASE WHEN "t2" > 0 THEN "t2" ELSE 0 END AS "残業",                   │
│       CASE WHEN "t3" > 0 THEN "t3" ELSE 0 END AS "深夜",                   │
│       "t0" - "t1"                             AS "欠勤"                    │
│  FROM (                                                                    │
│    SELECT trunc((extract(EPOCH FROM "e1" - "s1")                           │
│                + extract(EPOCH FROM "e2" - "s2")) / 60) AS "t1",           │
│           trunc((extract(EPOCH FROM "e3" - "s3")) / 60) AS "t2",           │
│           trunc((extract(EPOCH FROM "e4" - "s4")) / 60) AS "t3",           │
│           "t0"                                                             │
│      FROM (                                                                │
│        SELECT CASE WHEN t.s < r.s1 THEN r.s1 ELSE t.s END AS "s1",         │
│               CASE WHEN t.e > r.e1 THEN r.e1 ELSE t.e END AS "e1",         │
│               CASE WHEN t.s < r.s2 THEN r.s2 ELSE t.s END AS "s2",         │
│               CASE WHEN t.e > r.e2 THEN r.e2 ELSE t.e END AS "e2",         │
│               CASE WHEN t.s < r.s3 THEN r.s3 ELSE t.s END AS "s3",         │
│               CASE WHEN t.e > r.e3 THEN r.e3 ELSE t.e END AS "e3",         │
│               CASE WHEN t.s < r.s4 THEN r.s4 ELSE t.s END AS "s4",         │
│               CASE WHEN t.e > r.e4 THEN r.e4 ELSE t.e END AS "e4",         │
│               trunc((extract(EPOCH FROM "e1" - "s1")                       │
│                    + extract(EPOCH FROM "e2" - "s2")) / 60) AS "t0"        │
│            FROM t,r                                                        │
│      ) AS "z1"                                                             │
│  ) AS "z2"                                                                 │
└──────────────────────────────────────┘
ただし、翌朝深夜残業が…。「time」型は「25:00」とかいう概念がない…。

例えば「09:00」に出社し、「18:00」に退社した日の労働時間を
分単位で求めたいとする。
で、だいたい「12:00～13:00」はお昼休みで1時間休憩だったりして
8時間(480分)労働だったりするわけだ。
これをSQL文で求めるとこんな感じになる。
┌──────────────────────────────────────┐
│SELECT trunc((extract(EPOCH FROM '18:00'::time - '09:00'::time)             │
│            - extract(EPOCH FROM '13:00'::time - '12:00'::time)) / 60);     │
└──────────────────────────────────────┘
中身を説明すると…。
「time」型はタイムスタンプとは違って時間のみを管理する便利な型だ。
で、これをそのまま減算することにより、経過時間が取得できる
その経過時間は「interval」型という特殊な型なので、
これを分に換算する訳だが、「extract(EPOCH FROM …」で秒にしか換算できない
よってここからは自力で、60秒で割って「trunc()」で
端数を切り捨てるってことをしている。
でこれを、「総勤務時間－休憩時間」で差し引きすると労働時間が求まるって寸法だ。

実際に倣って差し引きを分単位で行いたいって場合なら、
おのおので一旦分換算するというのもある。
┌──────────────────────────────────────┐
│SELECT trunc((extract(EPOCH FROM '18:00'::time - '09:00'::time)) / 60)      │
│     - trunc((extract(EPOCH FROM '13:00'::time - '12:00'::time)) / 60);     │
└──────────────────────────────────────┘

お昼休みは特に時間帯は決まっていなくて
とにかく1時間休める(混んでない時に飯にいける)って場合であれば
経過時間をそのまま使えばよい
┌──────────────────────────────────────┐
│SELECT trunc((extract(EPOCH FROM '18:00'::time - '09:00'::time)             │
│            - extract(EPOCH FROM '01:00'::time                )) / 60);     │
└──────────────────────────────────────┘

ということで、これがわかれば、今ベタで書いている時間の部分を
「time」型のデータに置き換えて(なんなら文字型データをキャストしてもよいが)
労働時間の計算が自由自在にできてしまいそうだ。

特定の接頭辞を置換する例は以下のような感じとなる。
┌──────────────────────────────────────┐
│UPDATE 表 SET 列 = regexp_replace(列,'^接頭辞','');                         │
└──────────────────────────────────────┘

┌──────────────────────────────────────┐
│SELECT current_timestamp;                                                   │
└──────────────────────────────────────┘
の代替。
┌──────────────────────────────────────┐
│SELECT CASE                                                                 │
│         WHEN current_timestamp < '2001-01-01 00:00:00' THEN                │
│           current_timestamp + '49710 days' + '23295 seconds'               │
│         ELSE                                                               │
│           current_timestamp                                                │
│       END AS "now";                                                        │
└──────────────────────────────────────┘
viewにしてでもお使いください。

って、いいのかい？

「'49710 days' + '23295 seconds'」の根拠がわからん。
実地試験で一番近い値はこれだった。

ついでに、
┌──────────────────────────────────────┐
│SELECT current_date;                                                        │
└──────────────────────────────────────┘
の代替
┌──────────────────────────────────────┐
│SELECT CASE                                                                 │
│         WHEN current_timestamp < '2001-01-01 00:00:00' THEN                │
│           CAST(current_timestamp + '49710 days' + '23295 seconds' AS DATE) │
│         ELSE                                                               │
│           CAST(current_timestamp AS DATE)                                  │
│       END AS "date";                                                       │
└──────────────────────────────────────┘

所定の文字数まで穴埋めするには「lpad()」・「rpad()」が使える。
┌──────────────────────────────────────┐
│SELECT lpad(1::text  , 2, '_');                                     →「_1」│
│SELECT lpad(12::text , 2, '_');                                     →「12」│
│SELECT lpad(123::text, 2, '_');                                     →「12」│
├──────────────────────────────────────┤
│SELECT rpad(1::text  , 2, '_');                                     →「1_」│
│SELECT rpad(12::text , 2, '_');                                     →「12」│
│SELECT rpad(123::text, 2, '_');                                     →「12」│
└──────────────────────────────────────┘
先頭の「l」は「left」(左)、「r」は「right」(右)を意味する。
所定の文字数をはみ出した分は切り捨てられてしまうようだ
(「rpad()」の場合も左から採用され、右が切り捨てられるから注意)

  ちなみに、「repeat()」の代用もできる。
┌──────────────────────────────────────┐
│SELECT repeat('*', 3);                                             →「***」│
│SELECT lpad('', 3, '*');                                           →「***」│
│SELECT rpad('', 3, '*');                                           →「***」│
└──────────────────────────────────────┘

Excelなんかでも用いられる文字列を並べて棒グラフっぽく表示する例。
┌──────────────────────────────────────┐
│SELECT キー, repeat('*', 列) AS "graph" FROM 表;                            │
└──────────────────────────────────────┘

「avg()」と「sum()÷count()」に違いがあるか実験してみた。
┌──────────────────────────────────────┐
│WITH "data"("value") AS (                                                   │
│         SELECT trunc(random() * 10000)         --サンプル値の幅(0～9999)   │
│             FROM generate_series(1, 10000, 1)  --サンプル数(10000件)       │
│     )                                                                      │
│SELECT CASE                                                                 │
│         WHEN "avg" = ("sum" / "count") THEN '一致'                         │
│         ELSE                                '不一致'                       │
│       END AS "判定",                                                       │
│       "avg"           AS "平均",                                           │
│       "sum" / "count" AS "合計÷件数"                                      │
│    FROM (SELECT avg("value")   AS "avg",                                   │
│                 sum("value")   AS "sum",                                   │
│                 count("value") AS "count"                                  │
│              FROM "data"                                                   │
│         ) AS "as_data";                                                    │
└──────────────────────────────────────┘
違いはないみたい(同じロジックなのか？)。

ついでに実行計画をみてみた。
┌──────────────────────────────────────┐
│EXPLAIN                                                                     │
│SELECT avg(列) FROM 表;                                                     │
├──────────────────────────────────────┤
│Aggregate  (cost=コスト rows=行数 width=行幅)"                              │
│  ->  Seq Scan on 表  (cost=コスト rows=行数 width=行幅)"                   │
└──────────────────────────────────────┘
┌──────────────────────────────────────┐
│EXPLAIN                                                                     │
│SELECT sum(列) / count(列) FROM 表;                                         │
├──────────────────────────────────────┤
│Aggregate  (cost=コスト rows=行数 width=行幅)"                              │
│  ->  Seq Scan on 表  (cost=コスト rows=行数 width=行幅)"                   │
└──────────────────────────────────────┘
ほぼ同じ。ほんのちょっとだけ後者の方がコストが高いようだ。
＃やっぱりまわりくどいと最適化には悪影響なの？

「current_date」は「'now'::date」とも書ける。

┌──────────────────────────────────────┐
│SELECT 列 FROM 表 ORDER BY 列 ASC;                                          │
└──────────────────────────────────────┘
とすべきところを、誤って
┌──────────────────────────────────────┐
│SELECT 列 FROM 表 ORDER BY 表 ASC;                                          │
└──────────────────────────────────────┘
としてしまった。(「ORDER BY」の指定が列ではなく表)

でも、結果がちゃんと出力された。
一体、何をもってソートされたのだろう？

「GROUP BY」の場合は、どうなんだろう？
ということで、試行錯誤の末、
「GROUP BY 表」でエラーとならないSQL文を見つけ出した。
┌──────────────────────────────────────┐
│SELECT 表 FROM 表 GROUP BY 表;                                              │
└──────────────────────────────────────┘

っていうか、以下ができてしまうじゃないか。
┌──────────────────────────────────────┐
│SELECT 表 FROM 表;                                                          │
└──────────────────────────────────────┘

出力結果は「(■,■…)」みたいな感じで、型がその表名型だって。

ってことは、さっきの「ORDER BY」の件は、だいたい察しがつくわな。

割り算の場合、割る数(分母)を「0」にすることはできない(除数ゼロエラー)。
SQL文でも同じで、「division by zero」エラーが発生する。
┌──────────────────────────────────────┐
│SELECT 6 / 3;                                             →「2」(integer型)│
├──────────────────────────────────────┤
│SELECT 6 / 0;                             →エラー(ERROR:  division by zero)│
└──────────────────────────────────────┘
では、NULLは？
NULLは「0」なのか？分母にとれない？

いやいやそんなことはない。
NULLは何もかもをNULL色に染めてしまうのだ。
┌──────────────────────────────────────┐
│SELECT 6 / NULL;                                       →「NULL」(integer型)│
├──────────────────────────────────────┤
│SELECT NULL / 3;                                       →「NULL」(integer型)│
└──────────────────────────────────────┘
ということで、NULLについては除数ゼロエラーの対策が要らないというお話でした。
＃但し、システム毎に仕様があるだろうからそれに合わせるように注意が必要
  例えば、NULLが分母になる場合100%扱いにするとかの、ローカルルールについてだ