Converting UTC datetime to GMT+1.00 in Oracle with NEW_TIME

building semantic layer
1

Database stores date-time in a UTC format.
I want them to be displayed in a GMT +1:00 format.

The oracle database settings are set to +2.00 (select dbtimezone from dual)

Simply adding hours doesn’t help cause:
daylight saving settings are different in both formats

NEW_TIME function in Oracle should help:


NEW_TIME( TABLE.date , 'TimeZone1', ' TimeZone2')

But what values should I use voor TimeZone1 and TimeZone2
to obtain the wanted results?

Tiny :netherlands: (BOB member since 2004-11-10)

2

Hi
You want then to be displayed in GMT +1:00 format ok… try to type this query in sql viewer
select to_char(new_time(to_date(‘09151994 12:30 AM’,‘MMDDYYYY HH:MI AM’), ‘GMT’,‘hdt’),‘month DD,YYYY HH:MI AM’) from dual
it will work
regards
baharudeen

Baharudeen :india: (BOB member since 2005-08-31)

3

Baharudeen thanks for your reply but ‘GMT’ , ‘hdt’
isn’t working I end up with:

NEW_TIME (‘17/10/2005 08:15:54’ , ‘GMT’ , ‘hdt’ ')

answer:

16/10/2005 23:15:54
(it’s subtracting 9 hours)

The answer which I need is:

17/10/2005 10:15:54

Any one a clue which timezones I need and if this conversion takes into account daylight savings??

Tiny :netherlands: (BOB member since 2004-11-10)

4

hi
you want change it hdt
NEW_TIME (‘17/10/2005 08:15:54’ , ‘GMT’ , ‘ast’ or’adt’ or ‘bst’ or’bdt’)
any one do it where correct time is comming or not send replay
regards
baharudeen

Baharudeen :india: (BOB member since 2005-08-31)

5

I allready tried all of those combinations but with no luck…
In fact I tried every possible combination (which is allowed with NEW_TIME) but all combinations don’t bring me the answers which I need…

If I do a :


SELECT DBTIMEZONE FROM dual;

I get the answer +2:00

Internet-search gives me the following ifo:

 If the database time zone is set to ’US/Pacific’, then it will return 
 ’US/Pacific’. However, if the database time zone is set to the absolute 
 time zone offset, such as ’-07:00’, it returns ’-07:00’ since it may not 
 map to a unique time zone region considering that daylight savings may be 
 in effect.

But I can’t really understand if this is a good thing or a bad thing if you consider that I want to deal with daylight-savings??

Tiny :netherlands: (BOB member since 2004-11-10)

6

hi
Try to do it only 5 hours diffrent
select to_char(new_time(to_date(‘11032005 2:18 PM’,‘MMDDYYYY HH:MI PM’), ‘GMT’,‘ast’),‘month DD,YYYY HH:MI PM’) from dual
output comming like that
november 03,2005 05:18 AM
/

Baharudeen :india: (BOB member since 2005-08-31)

7

hi
try do to this one

Declare
a Timestamp with Time zone
:= To_Timestamp_tz (‘11032005 4:10 AM -4:12’,‘MMDDYYYY HH:MI AM TZH:TZM’);
begin
dbms_output.put_line(a);
dbms_output.put_line(to_char(new_time(a,‘GMT’,‘hdt’),‘month DD,YYYY HH:MI AM’));
end;
/
regards
baharudeen

Baharudeen :india: (BOB member since 2005-08-31)

8

Hi

WHAT HAPPEN YOU GOT A OUTPUT OTHER WISE
TRY TO DO LIKE THIS
SELECT TZNAME,TZABBREV FROM V$TIMEZONE_NAMES WHERE SUBSTR(TZ_OFFSET(TNAME),1,8) =’-07:000;

SELECT TZ_OFFSET(‘US/EASTERN’) OFFSET,
LENGNTH(TZ_OFFSET(‘US/EASTERN’)) LENGTH,
DUMP(TZ_OFFSET(US/EASTERN’)) DUMP FROM DUAL

REGARDS
BAHARUDEEN

Baharudeen :india: (BOB member since 2005-08-31)

9

despite all your suggestions none of them worked for me.(and some of them were not applicable for my situation)
If now worked around this issue using a CASE WHEN to get around this problem for the meantime…

I’m thinking of using a Calender table (which I populate myself)
to find a more structural solution for the problem …

Tiny :netherlands: (BOB member since 2004-11-10)