Wednesday, June 3, 2009

How to calculate time difference from TIMESTAMPs

The following script reads a table containing user actions and calculates the time difference (in hours) between the first and last action per user and work day.

The field action_tm is of type TIMESTAMP.

SELECT TRUNC (action_tm) work_day, user_nm,
       MIN (action_tm) start_time,
       MAX (action_tm) end_time,
       ROUND (((TO_CHAR (MAX (action_tm), 'SSSSS') + TO_CHAR (MAX (action_tm), 'FF') * .000001) -
               (TO_CHAR (MIN (action_tm), 'SSSSS') + TO_CHAR (MIN (action_tm), 'FF') * .000001)) /60/60,2) work_hours
FROM user_actions a
GROUP BY TRUNC (action_tm), user_nm
ORDER BY TRUNC (action_tm);

No comments: