SQLだけで勤怠データから曜日ごと、日中・深夜勤務時間を算出する（Postgres編）

こんにちは。千葉です。

開発者ブログをはじめよう、ということで初ポストです。

弊社には勤怠管理用の社内システムがあり（ちょうどいい勤怠システムってあんまりないですよね...！）そのメンテナンスもしています。

そこで今回「曜日や日中・深夜の勤務時間によって時間数を分けて表示してほしい」という案件が降ってきました。 素直にRuby on Railsで実装したり、適切な設計がしてあれば簡単な実装ですが、諸事情によりSQL(Postgres)だけで表示したい！ということになりました...！厄介ですね。

どうしよう、どうしようと先延ばしにしているうちに同じく開発部のエリックが考えてくれました。 方針としてはこうです。

• 曜日は EXTRACT(DOW FROM date) すれば簡単に出せるのでそれで判定
• 時間は LEASTとGREATEST を駆使して境目の時間ごとに判定

Locariでは普段MySQLを使っているので、Postgresの勉強になりました。

この方針にのっとって、下記のテーブルがあるとすると、 （実際のテーブルのカラムをいくつか省いています）

                                            Table "public.user_attendances"
          Column          |            Type             |                           Modifiers
--------------------------+-----------------------------+---------------------------------------------------------------
 id                       | integer                     | not null default nextval('user_attendances_id_seq'::regclass)
 status                   | integer                     | not null default 0
 user_id                  | integer                     | not null
 is_at_office             | boolean                     | not null default true
 date                     | date                        | not null
 started_at               | timestamp without time zone |
 ended_at                 | timestamp without time zone |

平日の日中の勤務時間を出すクエリーは下記のようになります。

• 実際には休憩時間も引く必要があるのでもうちょっと複雑になります
• DBにはUTCで保存されているので、9時間足しています。

SELECT
  "User Attendances"."user_id" AS "Id",
  EXTRACT(EPOCH
          FROM
          CASE WHEN EXTRACT(DOW FROM date) <> 0
            THEN
              GREATEST(
                  INTERVAL '0',
                  LEAST(
                      (started_at + INTERVAL '9 hours') :: DATE + INTERVAL '22 hours',
                      ended_at + INTERVAL '9 hours'
                  ) - GREATEST(
                      (started_at + INTERVAL '9 hours') :: DATE + INTERVAL '5 hours',
                      started_at + INTERVAL '9 hours'
                  )
              )
          ELSE INTERVAL '0'
          END
          +
          CASE WHEN EXTRACT(DOW FROM date + INTERVAL '1 days') <> 0 AND
                    ((started_at + INTERVAL '9 hours') :: DATE < (ended_at + INTERVAL '9 hours') :: DATE)
            THEN
              GREATEST(
                  INTERVAL '0',
                  LEAST(
                      (ended_at + INTERVAL '9 hours') :: DATE + INTERVAL '22 hours',
                      ended_at + INTERVAL '9 hours'
                  ) - (
                    (ended_at + INTERVAL '9 hours') :: DATE + INTERVAL '5 hours'
                  )
              )
          ELSE INTERVAL '0'
          END
  ) / 60                       AS "月～土通常時間"
FROM "public"."user_attendances" AS "User Attendances"
WHERE ("User Attendances"."date" BETWEEN {CALENDAR.START} AND {CALENDAR.END}
AND ("User Attendances"."status" = 1))

すごいですね...！久しぶりにこんなクエリーを書きました。 でもおかげで、正しい時間数が算出できるようになりました。

WondershakeではSQLを駆使してLocariや会社をよくしてくれる人を探しています！ よかったらご応募ください！