時間の差と集計

例）退社時間 - 始業時間 = 労働時間とし、その総時間を算出する

SELECT SUM(worktime_end - worktime_start) FROM mst_worktime

mst_worktime：労働時間テーブル
worktime_start（TIME型）：始業時間
worktime_end（TIME型）：退社時間

例）

system=# select id, created_at, updated_at from table where id in (12,17);

 id |         created_at         |         updated_at


























































































































-




























































 12 | 2010-05-31 20:42:42.306407 | 2011-02-28 13:07:10.579912

 17 | 2010-05-31 20:42:42.310003 | 2011-12-12 12:22:32.425819

time型

system=# select sum(cast(created_at as time) - cast(updated_at as time)) from table where id in (12,17);

       sum
































-















 15:55:41.610679

(1 行)

timestamp型

system=# select sum(updated_at - created_at) from table where id in (12,17);

           sum


















































-
























 831 days 32:04:18.389321

(1 行)

version 8.4.2