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
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.