Round up Hours and Minutes

[Imran]

Hi All,

I am trying to get round problem of the timestamp diff field giving incorrect rounding on hours and minutes. 

I used time stamp diff minutes and want to take total minutes and divide by 60, to get hours, so 70 mins would be 1.16' hours. Answers always rounds up, so this would become 2 hours. Is there a way of getting results to 2 decimal places. 

1 st Attemp: 

TIMESTAMPDIFF(SQL_TSI_MINUTE,"Execution Plan Run"."Execution Plan Start Time" , "Execution Plan Run"."Execution Plan End Time")/60.000000000001 

2 nd attempt: 

ROUND(cast(TIMESTAMPDIFF(SQL_TSI_MINUTE,"Execution Plan Run"."Execution Plan Start Time" , "Execution Plan Run"."Execution Plan End Time")/60 as real),3) 

3 rd attempt: 

TIMESTAMPDIFF(SQL_TSI_MINUTE,"Execution Plan Run"."Execution Plan Start Time" , "Execution Plan Run"."Execution Plan End Time")/cast(60 as real)

Nothing is working out...

Thanks 

Imran 

[Mohammad Farhan Alam]

Imran , 

I am sorry if this does not work but did you try 

http://oraclebisolutions.blogspot.com/2013/01/ceil-function.html

Regards, 

Mohammad 

--
--
You received this message because you are subscribed to the Google
Groups "OBIEE Enterprise Methodology Group" group.
To post to this group, send email to
obiee-enterpri...@googlegroups.com
To unsubscribe from this group, send email to
obiee-enterprise-met...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/obiee-enterprise-methodology?hl=en
 
All content to the OBIEE EMG lies under the Creative Commons Attribution 3.0 Unported License (http://creativecommons.org/licenses/by/3.0/). Any content sourced must be attributed back to the OBIEE EMG with a link to the Google Group (http://groups.google.com/group/obiee-enterprise-methodology).

---
You received this message because you are subscribed to the Google Groups "OBIEE Enterprise Methodology Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to obiee-enterprise-met...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Imran]

Thanks a lot Mohammed...

Not helpful link...I tried this below blog, but still rounding the Hours and minutes.

http://www.bravesoft.com/blog/?p=682

If any one have suggestion please do let me know.

Thanks advance

--

Thanks,

--

Imran M

BI Lead

Ph:949-439-2348

www.linkedin.com/pub/i-rahman/12/420/618/

http://imranrahmans.blogspot.com/

[Robert Tooker]

Try casting as double before the division - something like

cast(TIMESTAMPDIFF(SQL_TSI_MINUTE,"Execution Plan Run"."Execution Plan Start Time" , "Execution Plan Run"."Execution Plan End Time") as double)/60.0

[Imran]

Thanks Robert...still its giving me same results.

[Parameshwaran Venkataraman]

Hi Imran,

Is the issue is because OBIEE is not rounding the resultant timestampdiff value or is it not rounding up always?

Param

[Imran]

Hi Param,

Issue is rounding intervals for example :

Column 1 - Actual Start Date/Time = 18/04/2008 15:30:00

column 2 - Actual End Date/Time = 18/04/2008 17:45:00

column 3 - Actual Hours Difference

This last column should equal 2.15

but result I am getting is 2.00

------------

If I use in database like below code  i am getting results, I need solution related to OBIEE functionality.

SELECT ( CAST( SYSTIMESTAMP+2.00 AS DATE ) - CAST( SYSTIMESTAMP+1.16 AS DATE ) ) *24 

FROM dual; 

[Parameshwaran Venkataraman]

timestampdiff function takes two parameters of type timestamp. Maybe convert the date datatypes to timestamp and pass it to diff function. Maybe you can use SQL_TSI_HOURS directly if it works.

[Imran]

You mean like this below? Can you send me sql that should be great.

cast(TIMESTAMPDIFF(SQL_TSI_HOUR,"Execution Plan Run"."Execution Plan Start Time" , "Execution Plan Run"."Execution Plan End Time") as date)

[Parameshwaran Venkataraman]

Refer to the below. You need to apply the cast for each date parameter.

https://bischool.wordpress.com/2009/12/01/conversion-of-date-datatype-to-timestamp-in-obiee/

[Adrian Addidici]

Imram

It appears to me that the answer you are getting below using Roberts method is exactly what you have asked for.  The picture shows decimal numbers, not rounded up.

What am I missing ?  Can you show a few full examples of what it is doing and you want it to do.

Adrian

Sent from my iPhone

On 7 Jan 2015, at 21:32, Imran <imra...@gmail.com> wrote:

Thanks Robert...still its giving me same results.

<image.png>

[Imran]

Hi Adrian,

Best example is 

All the minutes difference are rounding to near value

1st Example-  E.g. 90 minutes then CONVERT it to be shown in hours so it would display 1.30

2nd example - highlighted in Yellow 3.92 is equal to  4.53 mints ?

fx formula - logic provided by Roberts

cast(TIMESTAMPDIFF(SQL_TSI_MINUTE,"Execution Plan Run"."Execution Plan Start Time" , "Execution Plan Run"."Execution Plan End Time") as double)/60.0000000001

Let me know if you need more information.

[Imran]

Any Luck on this ......If you have any other work around do let me know.

[Imran]

I followed one of the blog they provided me this info will it really work?

I tried but never worked for me.

CASE WHEN "Execution Plan Run"."Execution Plan Start Time" IS NOT NULL AND "Execution Plan Run"."Execution Plan End Time" IS NOT NULL THEN CAST(FLOOR (TimeStampDiff(SQL_TSI_MINUTE,"Execution Plan Run"."Execution Plan Start Time" ,"Execution Plan Run"."Execution Plan End Time")) AS VARCHAR(10))|| ':' || CASE WHEN LENGTH( CAST (FLOOR(TimeStampDiff(SQL_TSI_MINUTE,"Execution Plan Run"."Execution Plan Start Time","Execution Plan Run"."Execution Plan End Time")) AS VARCHAR(10))) < 2 THEN '0'|| CAST(ROUND (MOD( TimeStampDiff(SQL_TSI_MINUTE,"Execution Plan Run"."Execution Plan Start Time" ,"Execution Plan Run"."Execution Plan End Time"),60) ,0) AS VARCHAR(10)) ELSE CAST(ROUND(MOD(TimeStampDiff(SQL_TSI_MINUTE,"Execution Plan Run"."Execution Plan Start Time" ,"Execution Plan Run"."Execution Plan End Time"), 60) ,0) AS VARCHAR(10)) END ELSE NULL END

[Addidici]

Imran

I am still struggling to see what the problem is.

Please can you let me know which of the following is what you are looking for:

A. The Answer in Hours and Minutes (shown in date/time format)
B. The Answer in Hours (shown in decimal notation)

If A then I assume a simple Date/Time minus date/time will provide the answer, in number of seconds between them.  If you want the answer formatted to show Hours:minutes:Seconds then use NUMTODSINTERVAL
e.g.  numtodsinterval(round((END_TIME-START_TIME) *86400), 'second')
You may need to format the result to see hours and minutes.
Of course you call this using EVALUATE

If B then the below seems to work, 90 Minutes is 1.50 Hours.  If you Want it to show 1.30 hours then you are asking for Hours and  Minutes notation


Adrian

[RamC]

Imran, I'd try floor(cast(TIMESTAMPDIFF(SQL_TSI_MINUTE,"Execution Plan Run"."Execution Plan Start Time" , "Execution Plan Run"."Execution Plan End Time") as double)/60.0000000001)||':'||mod( cast(TIMESTAMPDIFF(SQL_TSI_MINUTE,"Execution Plan Run"."Execution Plan Start Time" , "Execution Plan Run"."Execution Plan End Time") as double),60)

Let us know if this works for you...

[Imran]

Hi All,

Once again Thanks you all

Here is the correct SQL for rounding issue... you will see output for 90 mints is 1hr 30 min

cast(cast(TRUNCATE(TIMESTAMPDIFF(SQL_TSI_MINUTE,"Execution Plan Run"."Execution Plan Start Time" , "Execution Plan Run"."Execution Plan End Time")/60, 0) as int) as char) || ' HR '  || cast(cast(TIMESTAMPDIFF(SQL_TSI_MINUTE,"Execution Plan Run"."Execution Plan Start Time" , "Execution Plan Run"."Execution Plan End Time") - (TRUNCATE(TIMESTAMPDIFF(SQL_TSI_MINUTE,"Execution Plan Run"."Execution Plan Start Time" , "Execution Plan Run"."Execution Plan End Time")/60, 0) * 60) as int)as char) || ' Min'