nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

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

Find more posts tagged with

Accepted answers

All comments

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

User_XN4EQ

TO_CHAR(FROM_TZ(CAST(CREATION_DATE AS TIMESTAMP), 'UTC') AT TIME ZONE 'Asia/Kolkata' ,'DD-MM-YYYY HH12:MI:SS AM')AS CREATION_DATE_IST

Riyaz Ali-Oracle

Review the below KM notes as it might be helpful:

Fusion BI Publisher - Time Zones Display - Best Practices of Coding (Doc ID 2256176.1)
Fusion BIP: How to get user preferred timezone in BIP reports (Doc ID 2425942.1)
Set Default Time Zone For All Users in Fusion Applications (Doc ID 1630493.1)
Legal Entity Time Zone -vs- Preferences Time Zone (Doc ID 1910933.1)
How To Allow User To Enter Transactions On The System Timezone Not User Timezone ? (Doc ID 2247516.1)
Oracle Fusion BI Publisher: Export To Excel From BIP Report - Date Format Is General And Doesn't Convert To Date (Doc ID 2080882.1)

Dimple-Nagesh-Support-Oracle

Hello Team,

Please check the following setup

Navigator > Setup & Maintenance > Manage Administrator Profile Options > ZCA_COMMON_CALENDAR > Please share the screenshots
Navigator > Setup & Maintenance > Manage Accounting Calendars > Edit the calendar and check for the year > If the Year is not equal to current year> Add Year upto current year i.e., 2023 or 2024 and Save and close.

can you please confirm the values of the following as Admin user
Login to application--> Username --> Set preferences --> Regional --> TimeZone --> Check what is the value set (attach the screenshots of the same)
Login to BI --> username --> Myaccount --> General Preferences --> Timezone --> Should be garyed out and should show the same value as in OSC (attach the screenshots of the same)
Login to BI --> username --> Myaccount --> BI Preferences --> Timezone --> Should show the same value as in OSC (attach the screenshots of the same)

If all the above are not same, please set it to the same value and run the Scheduled process "Refresh Denormalised Time Dimension Table for BI"

Login to application--> Navigator --> Scheduled Process --> Dropdown --> search --> Refresh Denormalised Time Dimension Table for BI" --> OK --> Submit.
Wait until success, then logout and clear your browser cache, re-login and retest the flow.