INTERVAL and TIMESTAMP conversions

A short one today all about dates and differences.

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.

How can I get the time difference from an INTERVAL DAY TO SECOND column?

Push the requirement back to the database if possible and we will use a 'trick' of SQL and dates to return a number result as if we had subtracted one date from the other. I'll leave the different calculations you may need to get the difference in hours etc... What I was interested in was the difference in seconds.

Assuming that the column in question is RUN_DURATION and has been defined as INTERVAL DAY(3) TO SECOND(2)

Aside/hint: We can add an interval to a date and the answer is date.

We will add the interval to a date and then subtract the date, yes I know it sounds like one of those trick mathematical quizzes that children are so fond of.

our RUN_DURATION is wrapped as follows

(SYSDATE+RUN_DURATION-SYSDATE)*86400 AS RUN_DURATION

The answer is given as the difference between two dates as standard in Oracle where 12 hours is 0.5 of a day. Multiplying the answer by 86400 gives me the answer in seconds.