SQL

◆ID10が当てはまるものすべて出力

SELECT COALESCE
((apply_end_time - apply_start_time)::time,'00:00'::time)
 - rest_time - COALESCE(over_rest_time,'00:00'::time) as 
work_time
FROM trn_work_time
WHERE user_id=10;

★サブクエリにして合計を算出出力

SELECT sum(work_time) from 
(SELECT 
  COALESCE((apply_end_time - 
apply_start_time)::interval,'00:00'::interval) 
   - COALESCE(rest_time::interval,'00:00'::interval)
   - COALESCE(over_rest_time::interval,'00:00'::interval)
    as work_time
FROM trn_work_time
WHERE user_id=10) AS work_time;

★group byで合計を求める

select
 SUM(COALESCE((apply_end_time -
apply_start_time),'00:00'::interval)
     - COALESCE(rest_time::interval,'00:00'::interval)
     - COALESCE(over_rest_time::interval,
    '00:00'::interval)
     )
FROM trn_work_time
WHERE user_id=10
group by user_id;

★間の秒数を表示

select extract( epoch FROM 
('24:00'::interval+ '24:00'::interval )) ;

★秒数を時間に変換して表示

select extract( epoch FROM 
('24:00'::interval+ '24:00'::interval ))* interval '1 second';

★秒数の引き算を表示

select extract( epoch FROM ('2009-01-31 00:00'::timestamp -
 '2009-01-29 00:00'::timestamp));

★サブクエリを使って×

select extract( epoch FROM (
select SUM(COALESCE((apply_end_time - 
apply_start_time),'00:00'::interval) 
     - COALESCE(rest_time::interval,'00:00'::interval)
     - COALESCE(over_rest_time::interval,
    '00:00'::interval)
     )
FROM trn_work_time
WHERE user_id=10
group by user_id
))* interval '1 second';

★サブクエリを使わずに○

select extract( epoch FROM (
SUM(COALESCE((apply_end_time - 
apply_start_time),'00:00'::interval)
     - COALESCE(rest_time::interval,'00:00'::interval)
     - COALESCE(over_rest_time::interval,'00:00'::interval)
     )))* interval '1 second'
FROM trn_work_time
WHERE user_id=10
group by user_id;