Oracle Time Zone Conversion

I have to prepare a report where the start date and end date was stored in GMT and I had to show the no of days it took to complete.
The logic is very simple no matter what it the time, just consider the date. Like if it has Start date as 2012-Dec-21 10:30:00 PM and End Date 2012-Dec-22 12:30:00 AM, it should be shown as 1 day even though it has taken just 2 and half hours.
So I started very cool as below

Select (trunc(end_date) - trunc(begin_date)) from wf_tasks

Now came the timezone into picture. Most of the records were correct but very few was having some issue as this is related to tasks and people doesn't work late at office :).

Consider the scenario when an user started a task on 9:30AM ET on Dec/21/2012 and completed the task (end_task column) at 9:30PM ET on Dec/22/2012. So in database the converted date saved as
Start Date (begin_date): 21-12-2012 2:30:00 PM
End Date(end_date): 23-12-2012 2:30:00 AM
(consider daylight saving off).
So when you run below query, it will give you 2 days but actuall the user started on 21st and completed on 22nd as per his Eastern time zone. So it should have shown 1 days.

Select
(
  TRUNC(TO_DATE('2012-12-23 2:30:00 AM','YYYY-MM-DD HH:MI:SS AM'))
  -
  TRUNC(TO_DATE('2012-12-21 2:30:00 PM','YYYY-MM-DD HH:MI:SS AM') )
)as No_Of_Days
from dual
--Returns 2 days

Resolution:-
I came to know about FROM_TZ function in oracle which Return Values TIMESTAMP_TZ
Syntax
FROM_TZ (timestamp_value , time_zone_value).


Rewriting the query considering the time zone:

Select 
(
  TRUNC(cast((FROM_TZ(CAST(TO_DATE('2012-12-23 2:30:00 AM','YYYY-MM-DD HH:MI:SS AM') AS TIMESTAMP),'GMT') AT TIME ZONE 'America/New_York')as Date))
  - 
  TRUNC(cast((FROM_TZ(CAST(TO_DATE('2012-12-21 2:30:00 PM','YYYY-MM-DD HH:MI:SS AM') AS TIMESTAMP),'GMT') AT TIME ZONE 'America/New_York')as Date))
)as No_Of_Days
from dual 

--Returns 1 Day

My Final Query looked like below with the columns storing date values.

Select
(
  TRUNC(cast((FROM_TZ(cast( end_date as Timestamp),'GMT') AT TIME ZONE 'America/New_York')as Date))
  -
  TRUNC(cast((FROM_TZ(cast( begin_date as Timestamp),'GMT') AT TIME ZONE 'America/New_York')as Date))
)as No_Of_Days
from wf_tasks

To get the zones abbreviation run below query:
SELECT tzname, tzabbrev FROM V$TIMEZONE_NAMES
SELECT UNIQUE tzname FROM V$TIMEZONE_NAMES;

Refrence:
http://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_1088.htm