Categories
Convert UTC to Local Time in Oracle BI Publisher
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
Answers
-
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> )<br/>*</span>Also there is bunch of info -> https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm
Hope this helps
--YG
0 -
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
0 -
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.
0 -
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
0 -
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
0 -
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)0 -
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.0
