Configure Timestamps

In Oracle Analytics, administrators set the default time zone used for date and time calculations in System Settings. This global setting applies to all calculations. This topic explains how to use the TIMESTAMPADD function to specify another time zone if you want to use a different timestamp in a particular calculation.

Set the Default Time Zone for Calculations in System Settings

By default, 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()

As an administrator, you can change the timestamp used in calculations using the Default Time Zone for Date Calculations option in the Console, under Advanced System Settings. Select the desired time zone from the drop-down list and apply the changes for the new value to take effect.

Description of ceal_default_time_zone_date_calculations.png follows
Description of the illustration ceal_default_time_zone_date_calculations.png​

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 is the interval and the time offset is 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. You can create the new timestamp column as a derived column in the logical table source or map it directly to the database The difference is where the calculation is to be performed; that is, Oracle Analytics or the database. The following figure shows a calculation mapped directly to the database in the Expression Builder - Expression dialog.

Description of ceal_timestampadd.jpg follows
Description of the illustration ceal_timestampadd.jpg

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)

The following figure shows a calculation that's created as a derived column in the Expression Builder - Derived logical column dialog.

Description of ceal_cast_timestampadd.jpg follows
Description of the illustration ceal_cast_timestampadd.jpg

For unique or singular 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 Date and Time Functions and Time Series Functions.