Date time with Timezone.

[Former Member]

Hi

I think this question is posted so many times before but I am not getting the clear idea how to handle date time with time zone when working with xMII 11.5 and oracle 10.

I am running the following query in toad and in query template, but are producing different results.

select to_char(cast(cast(to_timestamp_tz('2010-04-23 20:30:00','yyyy-mm-dd hh24:mi:ss.ff+tzh:tzm')as TIMESTAMP with local time zone)as date),'DD/MM/YYYY HH24:MI:SS') as d1,

to_char(cast(cast(to_timestamp_tz('2010-04-23 20:30:00+08:00','yyyy-mm-dd hh24:mi:ss.ff+tzh:tzm')as TIMESTAMP with local time zone)as date),'DD/MM/YYYY HH24:MI:SS') as d2,

to_char(cast(cast(to_timestamp_tz('2010-04-23 20:30:00+09:00','yyyy-mm-dd hh24:mi:ss.ff+tzh:tzm')as TIMESTAMP with local time zone)as date),'DD/MM/YYYY HH24:MI:SS') as d3 from dual

The result in Toad is

D1     23/04/2010 20:30:00
D2     23/04/2010 20:30:00
D3     23/04/2010 19:30:00

The result in MII QueryTemplate is

D1   23/04/2010 20:30:00 
D2   23/04/2010 12:30:00
D3   23/04/2010 11:30:00

Clearly, MII is returning UTC time instead of absolute time. Any clarification will be appreciate.

Regards

Rupesh

[khaleelurrehman_badeghar]

Hi Rupesh,

Please try this.

select to_char(cast(cast(to_timestamp_tz('2010-04-23 20:30:00.00','yyyy-mm-dd hh24:mi:ss.ff+tzh:tzm')as TIMESTAMP with local time zone)as date),'DD/MM/YYYY HH24:MI:SS') as d1,
 
to_char(cast(cast(to_timestamp_tz('2010-04-23 20:30:00.00+08:00','yyyy-mm-dd hh24:mi:ss.ff+tzh:tzm')as TIMESTAMP with local time zone)as date),'DD/MM/YYYY HH24:MI:SS') as d2,
 
to_char(cast(cast(to_timestamp_tz('2010-04-23 20:30:00.00+09:00','yyyy-mm-dd hh24:mi:ss.ff+tzh:tzm')as TIMESTAMP with local time zone)as date),'DD/MM/YYYY HH24:MI:SS') as d3 from dual

Hope this helps.

Thanks and Regards

Khaleel Badeghar

[Former Member]

Hi Khaleel,

How are you,

Unfortunately this query returns the same value. We solved our problem by sticking to Local Time zone.

Thanks

Rupesh