Working with date differences (TIMESTAMPDIFF)

TIMESTAMPDIFF

Unlike in the Oracle database the difference between two dates is not as simple as subtracting one date from the other (oh if only....), but we do have a system that is actually flexible in other interesting ways.

Don't forget, OBIEE at the business modelling level and in answers has no concept of the type of database your data is coming from, it could be one database or multiple. OBIEE will translate the date calculations, where possible, and push back down into the database layer if the date calculation is done in the repository.

The syntax is:

 TimestampDiff(interval, timestamp1, timestamp2)

So where's the flexibility.... it is in the interval parameter.

We can ask for the difference between the two dates in differing units of

• SQL_TSI_SECOND, 
• SQL_TSI_MINUTE, 
• SQL_TSI_HOUR, 
• SQL_TSI_DAY, 
• SQL_TSI_WEEK, 
• SQL_TSI_MONTH, 
• SQL_TSI_QUARTER, 
• SQL_TSI_YEAR

For example 

TimestampDiff(SQL_TSI_MONTH, TIMESTAMP'1999-07-31 00:00:00', TIMESTAMP'1999-12-31 00:00:00') 

would give you 5

Conditional format on a null

If you want to highlight the empty columns in your report, and make them stand out visually you want to look at adding a condition on the is null operator.

<image>