Showing posts with label difference. Show all posts
Showing posts with label difference. Show all posts

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);