Timestamps

In Oracle Analytics, administrators set the default time zone used for date and time calculations in System Settings, and this global setting applies to all calculations. If you want to use a different timestamp in a particular calculation, you can use the TIMESTAMPADD function to specify the desired time zone.

Set the Default Time Zone for Calculations in System Settings

By default, the system time forOracle 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()

Administrators can change the timestamp used in calculations using the Default Time Zone for Date Calculations option in the Console, under System Settings. Select the desired time zone from the dropdown and apply the changes for the new value to take effect.

Description of the illustration ceal_default_time_zone_date_calculations.jpg​

For more information about global time zone settings, see System Settings - Format Options.

Use a Specific Time Zone for Timestamps in Calculations

To generate a timestamp other than the system setting in your calculations, use the TIMESTAMPADD function in conjunction with CURRENT_TIMESTAMP() or NOW(). The basic TIMESTAMPADD calculation is:

TIMESTAMPADD(SQL_TSI_<<interval>>, <<integer expression>>, <<timestamp expression>>)

For timestamp calculations, HOUR will be the interval and the time offset will be the difference in hours between UTC and the time zone to be reported on. For example:

TIMESTAMPADD(SQL_TSI_HOUR, -6,CURRENT_TIMESTAMP(5))

This calculation returns the current timestamp in US Mountain time. The new timestamp column can be created as a derived column in the logical table source or mapped directly to the database, the difference being where the calculation is to be performed, that is, Oracle Analytics or the database. Here, the calculation is mapped directly to the database:

Description of the illustration ceal_timestampadd.jpg

In another case, when the default time zone is set to UTC, the CURRENT_DATE function doesn't return the correct date for certain hours of the day relative to a user’s time zone. For example, when it’s midnight UTC, the local date doesn't match the UTC date for the duration of the time offset.

Casting the timestamp calculation as a date resolves this issue. For example:

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

Here, the calculation is created as a derived column:

Description of the illustration ceal_cast_timestampadd.jpg

For one-off reports, you can create timestamp calculations at the report level in either a data visualization or a classic analysis. If you plan to reuse the calculations, it's often easier to define them as columns in the semantic model (or RPD file).

For more information about the functions mentioned in this topic, see Time and Date Functions and Time Series Functions.