Categories
- All Categories
- 127 Oracle Analytics News
- 23 Oracle Analytics Videos
- 14.5K Oracle Analytics Forums
- 5.5K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 47 Oracle Analytics Trainings
- 7 Oracle Analytics Data Visualizations Challenge
- 4 Oracle Analytics Career
- 8 Oracle Analytics Industry
- Find Partners
- For Partners
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