OBIEE and Oracle TIMESTAMP WITH TIME ZONE columns

A short one today.

I recently had cause to report off a table (lots of dynamic real-time stuff) where there was a column defined as TIMESTAMP(6) WITH TIME ZONE.

Unfortunately OBIEE doesn't seem to understand this too well, and anyway all I'm interested in is the date and time, all the timezone stuff is not required for reporting (phew).

There is a way to change the TIMESTAMP(6) WITH TIME ZONE column to a date and it passes a couple of the best practice tests, mainly that it is performed in the database, as far back down the chain as possible and secondly that no new invented code is used, only existing Oracle functionality.

The column in question is LOG_DATE defined as TIMESTAMP(6) WITH TIME ZONE, and to change this to a date column use CAST.

CAST (log_date AS DATE) AS log_date

As the command is fairly self explanatory, I'll stop here.