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