469,127 Members | 1,306 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,127 developers. It's quick & easy.

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

dlite922
1,584 Expert 1GB
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
Expand|Select|Wrap|Line Numbers
  1. TO_CHAR(e.begin_time,'HH24:MI') BEGIN_UTC
  2.  
Okay, let's convert that to US/Eastern:

This Yeilds 20:30 (Makes sense, 01:30-5:00=20:30)
Expand|Select|Wrap|Line Numbers
  1. TO_CHAR(CAST((FROM_TZ(CAST(e.begin_time AS TIMESTAMP),'UTC') AT TIME ZONE 'US/Eastern') AS DATE),'HH24:MI') EST_BEGIN
  2.  
So let's take 20:30, and convert back to UTC just to test a clause that does this:

Expand|Select|Wrap|Line Numbers
  1. TO_CHAR(FROM_TZ(TO_TIMESTAMP('20:30','HH24:MI'),'US/Eastern') AT TIME ZONE 'UTC','HH24:MI') EST_2030
  2.  
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
Nov 7 '11 #1
1 12632
dlite922
1,584 Expert 1GB
Semi-answer

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

Expand|Select|Wrap|Line Numbers
  1. 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')
  2.  
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.
Nov 7 '11 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by flupke | last post: by
1 post views Thread by heirou | last post: by
5 posts views Thread by Paulers | last post: by
3 posts views Thread by moni | last post: by
18 posts views Thread by moni | last post: by
3 posts views Thread by Evan Klitzke | last post: by
19 posts views Thread by DonLi2006 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.