Oracle Time Conversion from EST to UTC and Back is 1 hour off?

[dlite922]

I initially thought this had to do with Daylight savings time change, but now that it has passed in the US, I looked at the query and now I observed this behavior:

begin_time is my column and is assumed to be UTC+0.

This Yields 01:30

TO_CHAR(e.begin_time,'HH24:MI') BEGIN_UTC
 

Okay, let's convert that to US/Eastern:

This Yeilds 20:30 (Makes sense, 01:30-5:00=20:30)

TO_CHAR(CAST((FROM_TZ(CAST(e.begin_time AS TIMESTAMP),'UTC') AT TIME ZONE 'US/Eastern') AS DATE),'HH24:MI') EST_BEGIN
 

So let's take 20:30, and convert back to UTC just to test a clause that does this:

TO_CHAR(FROM_TZ(TO_TIMESTAMP('20:30','HH24:MI'),'US/Eastern') AT TIME ZONE 'UTC','HH24:MI') EST_2030
 

You'd think it would return 01:30, it doesn't. the result is 00:30 (one hour before)

Am I doing one of the conversions wrong?

Thanks for any help, I'm running low on Coffee today I guess.

Dan

[dlite922]

Semi-answer

If I give it the day as well, (today's date), it yields 01:30 AM correctly.

TO_CHAR(FROM_TZ(TO_TIMESTAMP('2011-11-07 20:30','YYYY-MM-DD HH24:MI'),'US/Eastern') AT TIME ZONE '+00:00','HH24:MI')
 

Why doesn't Oracle assume Daylight Savings time (on/off) for today automatically when no day is given?

Weird. I guess I'll code in today's date automatically.