Decimal difference to hours and minutes in OBIEE

Just has one of those issues of different interpretations of the same item by different people in different ways.

Actually, if I'm honest, I'm glad someone's out there manually checking all the hard work we put in here.

And what is this magical value that causes so much confusion - the difference between two time values displayed as a number of hours. Does the value 5.08 mean a. five hours and eight hundredths of an hour, or b. five hours and eight minutes.

Turns out it's we thought it was the first - five hours and eight hundredths of an hour and (some of) the users thought it was five hours and eight minutes.

In the interests of sanity it was decided that a disclaimer should be put against the report in question to clarify the issue and all metadata altered to definitively reflect that this was an decimal representation of hours and fractions thereof.

But the question arose - how difficult is it to show the value 5.08 as hours and minutes.

To derive 5.08 in the database we had subtracted the start date from the end date and multiplied the result by 24 giving us the difference in hours. Oracle does offer us the opportunity to do this calculation and have the result as an interval (day-to-second), but that gets stuck in the database hence the numeric difference in hours.

Thinking the solution through it is immediately apparent the first number is the hours regardless of what comes after the decimal point. For this we can use the FLOOR function which returns 5.

FLOOR(5.08) = 5

calculating minutes is working with what's left after we remove the hours.

5.08 - FLOOR(5.08) = 0.08

which in minutes is

60*0.08 = 4.8

Which is very different to the 8 minutes assumed by some users. We could carry on and work out the seconds implied by the 0.8 of a minute, but for the purpose of this discussion, rounding the result is sufficient. Putting the last piece together gives

ROUND(60*(5.08 - FLOOR(5.08)))

so sticking it all together to make it look pretty means we'll have to cast our numbers to CHAR

CAST(FLOOR(5.08) AS CHAR) || ' hours and ' || CAST(ROUND(60*(5.08 - FLOOR(5.08))) AS CHAR) || ' minutes'

Problem solved.

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.

Calendar Date and Time Functions

Calendar Date/Time Functions

The calendar date/time functions manipulate data of the data types DATE and DATETIME based on a calendar year. You must select these functions together with another column; they cannot be selected alone.

Functions

Current_Date
Current_Time
Current_TimeStamp
Day_Of_Quarter
DayName
DayOfMonth
DayOfWeek
DayOfYear
Hour
Minute
Month
Month_Of_Quarter
MonthName
Now
Quarter_Of_Year
Second
TimestampAdd
TimestampDiff
Week_Of_Quarter
Week_Of_Year
Year

---

CURRENT_DATE

Returns the current date. The date is determined by the system in which the Oracle BI Server is running.

Syntax

Current_Date

---

CURRENT_TIME

Returns the current time. The time is determined by the system in which the Oracle BI Server is running.
Note: The Analytics Server does not cache queries that contain this function.

Syntax

Current_Time(integer)

Where:

integer

Any integer that represents the number of digits of precision with which to display the fractional second.

---

CURRENT_TIMESTAMP

Returns the current date/timestamp. The timestamp is determined by the system in which the Oracle BI Server is running.
Note: The Oracle BI Server does not cache queries that contain this function.

Syntax

Current_TimeStamp(integer)

Where:

integer

Any integer that represents the number of digits of precision with which to display the fractional second.

---

DAY_OF_QUARTER

Returns a number (between 1 and 92) corresponding to the day of the quarter for the specified date.

Syntax

Day_Of_Quarter(dateExpr)

Where:

dateExpr

Any expression that evaluates to a date.

---

DAYNAME

Returns the name of the day for a specified date.

Syntax

DayName(dateExpr)

Where:

dateExpr

Any expression that evaluates to a date.

---

DAYOFMONTH

Returns the number corresponding to the day of the month for a specified date.

Syntax

DayOfMonth(dateExpr)

Where:

dateExpr

Any expression that evaluates to a date.

---

DAYOFWEEK

Returns a number between 1 and 7 corresponding to the day of the week, Sunday through Saturday, for a specified date. For example, the number 1 corresponds to Sunday and the number 7 corresponds to Saturday.

Syntax

DayOfWeek(dateExpr)

Where:

dateExpr

Any expression that evaluates to a date.

---

DAYOFYEAR

Returns the number (between 1 and 366) corresponding to the day of the year for a specified date.

Syntax

DayOfYear(dateExpr)

Where:

dateExpr

Any expression that evaluates to a date.

---

HOUR

Returns the number (between 0 and 23) corresponding to the hour for a specified time. For example, 0 corresponds to 12 A.M. and 23 corresponds to 11 P.M.

Syntax

Hour(timeExpr)

Where:

timeExpr

Any expression that evaluates to a time.

---

MINUTE

Returns the number (between 0 and 59) corresponding to the minute for a specified time.

Syntax

Minute(timeExpr)

Where:

timeExpr

Any expression that evaluates to a time.

---

MONTH

Returns a number (between 1 and 12) corresponding to the month for a specified date.

Syntax

Month(dateExpr)

Where:

dateExpr

Any expression that evaluates to a date.

---

MONTH_OF_QUARTER

Returns the number (between 1 and 3) corresponding to the month in the quarter for a specified date.

Syntax

Month_Of_Quarter(dateExpr)

Where:

dateExpr

Any expression that evaluates to a date.

---

MONTHNAME

Returns the name of the month for a specified date.

Syntax

MonthName(dateExpr)

Where:

dateExpr

Any expression that evaluates to a date.

---

NOW

Returns the current timestamp. This function is equivalent to the function current_timestamp .

Syntax

Now()

---

QUARTER_OF_YEAR

Returns the number (between 1 and 4) corresponding to the quarter of the year for a specified date.

Syntax

Quarter_Of_Year(dateExpr)

Where:

dateExpr

Any expression that evaluates to a date.

---

SECOND

Returns the number (between 0 and 59) corresponding to the seconds for a specified time.

Syntax

Second(timeExpr)

Where:

timeExpr

Any expression that evaluates to a time.

---

TIMESTAMPADD

Adds a specified number of intervals to a specified timestamp, and returns a single timestamp. Passing a null intExpr or timeExpr to this function results in the return of a null value.
In the simplest scenario, this function simply adds the specified integer value to the appropriate component of the timestamp, based on the interval. Adding a week translates to adding seven days, and adding a quarter translates to adding three months. A negative integer value results in a subtraction (such as going back in time).
An overflow of the specified component (for example, more than 60 seconds, 24 hours, twelve months, and so on) necessitates adding an appropriate amount to the next component. For example, when adding to the day component of a timestamp, this function makes sure that overflow takes into account the number of days in a particular month (including leap years). Similar measures are used to make sure that adding a month component results in the appropriate number of days for the day component (such as adding a month to '2010-05-31' does not result in '2010-06-31' because June does not have 31 days). The function also deals with the month and day components in a similar fashion when adding or subtracting year components.

Syntax

TimestampAdd(interval, intExpr, timestamp)

Where:

interval

The specified interval. Valid values are: SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR.

intExpr

Any expression that evaluates to an integer value.

timestamp

Any valid timestamp.

Examples

Select {TimestampAdd(SQL_TSI_DAY, 3, TIMESTAMP'2000-02-27 14:30:00')}
From Employee where employeeid = 2;

In the above example, the query asks for the resulting timestamp when 3 days are added to '2000-02-27 14:30:00'. Since February, 2000 is a leap year, the query returns a single timestamp of '2000-03-01 14:30:00'.

Select {TimestampAdd(SQL_TSI_MONTH, 7, TIMESTAMP'1999-07-31 00:00:00')}
From Employee where employeeid = 2;

In the above example, the query asks for the resulting timestamp when 7 months are added to '1999-07-31 00:00:00'. The query returns a single timestamp of '2000-02-29 00:00:00'. Notice the reduction of day component to 29 because of the shorter month of February.

Select {TimestampAdd(SQL_TSI_MINUTE, 25, TIMESTAMP'2000-07-31 23:35:00')}
From Employee where employeeid = 2;

In the above example, the query asks for the resulting timestamp when 25 minutes are added to '2000-07-31 23:35:00'. The query returns a single timestamp of '2000-08-01 00:00:00'. Notice the propagation of overflow through the month component.

---

TIMESTAMPDIFF

Returns the total number of specified intervals between two timestamps. Passing a null timestamp to this function results in a null return value.
This function first determines the timestamp component that corresponds to the specified interval parameter, and then looks at the higher order components of both timestamps to calculate the total number of intervals for each timestamp. For example, if the specified interval corresponds to the month component, the function calculates the total number of months for each timestamp by adding the month component and twelve times the year component. Then the function subtracts the first timestamp's total number of intervals from the second timestamp's total number of intervals.
The TimestampDiff function rounds up to the next integer whenever fractional intervals represent a crossing of an interval boundary. For example, the difference in years between '1999-12-31' and '2000-01-01' is 1 year because the fractional year represents a crossing from one year to the next (such as 1999 to 2000). By contrast, the difference between '1999-01-01' and '1999-12-31' is zero years because the fractional interval falls entirely within a particular year (such as 1999). Microsoft's SQL Server exhibits the same rounding behavior, but IBM's DB2 does not; it always rounds down. Oracle does not implement a generalized timestamp difference function.
When calculating the difference in weeks, the function calculates the difference in days and divides by seven before rounding. Additionally, the function takes into account how the administrator has configured the start of a new week in the NQSConfig.ini file. For example, with Sunday as the start of the week, the difference in weeks between '2000-07-06' (a Thursday) and '2000-07-10' (the following Monday) results in a value of 1 week. With Tuesday as the start of the week, however, the function would return zero weeks since the fractional interval falls entirely within a particular week. When calculating the difference in quarters, the function calculates the difference in months and divides by three before rounding.
Oracle BI Server pushes down the TIMESTAMPADD and TIMESTAMPDIFF functions to Microsoft's SQL Server and ODBC databases by default. While Oracle BI Server can also push to IBM's DB2, the features table is turned off by default due to DB2's simplistic semantics. (IBM's DB2 provides a generalized timestamp difference function, TIMESTAMPDIFF, but it simplifies the calculation by always assuming a 365-day year, 52-week year, and 30-day month.) The features table is also turned off by default for Oracle, since Oracle databases do not fully support these functions.

Syntax

TimestampDiff(interval, timestamp1, timestamp2)

Where:

interval

The specified interval. Valid values are: SQL_TSI_SECOND, SQL_TSI_MINUTE, SQL_TSI_HOUR, SQL_TSI_DAY, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, SQL_TSI_YEAR.

timestamp1

Any valid timestamp.

timestamp2

Any valid timestamp.

Examples

Select {TimestampDiff(SQL_TSI_DAY, TIMESTAMP'1998-07-31 23:35:00', TIMESTAMP'2000-04-01 14:24:00')}
From Employee where employeeid = 2;

In the above example, the query asks for a difference in days between timestamps '1998-07-31 23:35:00' and '2000-04-01 14:24:00'. It returns a value of 610. Notice that the leap year in 2000 results in an additional day.

---

WEEK_OF_QUARTER

Returns a number (between 1 and 13) corresponding to the week of the quarter for the specified date.

Syntax

Week_Of_Quarter(dateExpr)

Where:

dateExpr

Any expression that evaluates to a date.

---

WEEK_OF_YEAR

Returns a number (between 1 and 53) corresponding to the week of the year for the specified date.

Syntax

Week_Of_Year(dateExpr)

Where:

dateExpr

Any expression that evaluates to a date.

---

YEAR

Returns the year for the specified date.

Syntax

Year(dateExpr)

Where:

dateExpr Any expression that evaluates to a date.

What Hour is it?

You can extract time elements from dates in OBIEE at both the repository and answers level.

Just use something along the lines of :

CAST ( EXTRACT( HOUR FROM "CentralCal"."date") AS INTEGER )