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

まずは前回同様、「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」とかいう概念がない…。