X

Oracle Customer Engineering & Advocacy Lab (CEAL) Blog covers Oracle Analytics Cloud, Oracle Analytics Server and...

Timestamps in Oracle Analytics Cloud

Timestamps in Oracle Analytics Cloud

Issue

The system time for Oracle Analytics Cloud is based on UTC time. The following functions return values using the UTC time or date:

CURRENT_DATE

CURRENT_TIME()

CURRENT_TIMESTAMP()

NOW()

By design, the time offsets that you see when you access the System Settings in the Console do not affect these functions.

Workaround

To generate the correct timestamp, use the TIMESTAMPADD function. This example returns the current timestamp in US Mountain time:

TIMESTAMPADD(SQL_TSI_HOUR, -6,CURRENT_TIMESTAMP)

When you use the CURRENT_DATE function, for some hours of the day, the function returns the correct date. However, when it’s midnight in UTC time, the local date won’t match the UTC date for the duration of the time offset.

 In that case, casting the example calculation as a date resolves the problem:

cast(TIMESTAMPADD(SQL_TSI_HOUR, -6,CURRENT_TIMESTAMP) as date)

You can create calculations at the report level in data visualization projects or in analyses. For ease of use, create the calculations as columns in the metadata repository (RPD).  You can then substitute the calculations wherever a CURRENT function is used, as shown in these examples from Expression Builder.  Create a current date column to use with the substitution for CURRENT_DATE in the Expression Editor.  You can use this expression for each subject area that is using a CURRENT function.

Instead of using the expression:

 CURRENT_TIMESTAMP() or NOW()

Enter the following in the Expression Editor to get the current time:

TIMESTAMPADD(SQL_TSI_HOUR, -6,current_timestamp)

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.