Convert UTC to Local Time in Oracle BI Publisher

Tiger Liu-55147

Summary

Convert UTC to Local Time in Oracle BI Publisher

Content

We need to convert UTC time to local time (Sydney) in Oracle BI Publisher XML output in Oracle Cloud ERP.

Since we cannot use BI template to handle the timezone conversion, we need to put this into the SQL.

however, the FROM_TZ doesn't seem to do the conversion at all.

Any idea what I am doing wrong here?

Thanks a lot.

select invoice_id,

last_update_date,

FROM_TZ(last_update_date, DBTIMEZONE)  AT TIME ZONE 'Australia/Sydney' as local_time

from ap_invoices_all

YGUTTIKONDA

can you try below samples

SELECT FROM_TZ(CAST(TO_DATE('1999-12-01 11:00:00',     'YYYY-MM-DD HH:MI:SS') AS TIMESTAMP), 'America/New_York')     AT TIME ZONE 'America/Los_Angeles' "West Coast Time"FROM DUAL<span class="pun"><br/></span><code><span class="kwd">* SELECT</span><span class="pln"> resolved_time AT TIME ZONE </span><span class="str">'Australia/Sydney'</span><span class="pln"> </span><span class="kwd">AS</span><span class="pln"> resolved_time_Aus </span><span class="kwd">FROM</span><span class="pln"> yourtable</span><span class="pun">; (<a href="https://stackoverflow.com/questions/30297293/convert-gmt-time-to-aestaustralian-timing-in-sql/30297885#30297885" title="https://stackoverflow.com/questions/30297293/convert-gmt-time-to-aestaustralian-timing-in-sql/30297885#30297885">https://stackoverflow.com/questions/30297293/convert-gmt-time-to-aestaustralian-timing-in-sql/30297885#30297885</a&gt; )<br/>*</span>

Also there is bunch of info -> https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm

Hope this helps

--YG

Tiger Liu-55147

Thanks YG.

I think it because BIP is converting the date back to UTC.

If I put to_char, both queries work.

Thanks.

SELECT TO_CHAR( FROM_TZ(CREATION_DATE, DBTIMEZONE) at time zone 'Australia/Sydney' ,'DD-MON-YYYY HH24:MI:SSxFF TZH:TZM') cr_dat_tz,

CREATION_DATE creation_date FROM ap_invoices_all

SELECT to_char(last_update_date AT TIME ZONE 'Australia/Sydney', 'DD-MON-YYYY HH24:MI:SSxFF TZH:TZM') AS last_update_date_Aus, last_update_date FROM ap_invoices_all

YGUTTIKONDA

for BI Publisher the date from the XML data source must be in canonical format. This format is:YYYY-MM-DDThh:mm:ss+HH:MM

Below are couple good blog posts from Product Team

https://blogs.oracle.com/xmlpublisher/date-functions

https://blogs.oracle.com/xmlpublisher/how-to-keep-your-dates-from-going-wild

Mark completed / correct if this resolved your issue.

Viswanath-Oracle

below should work for you

SELECT TO_CHAR(FROM_TZ(CAST(last_update_date AS TIMESTAMP), 'UTC') AT TIME ZONE 'Australia/Sydney','DD-MM-YYYY HH24:MI:SS') AS last_update_date_Aus, last_update_date FROM ap_invoices_all