12 Replies Latest reply: Oct 10, 2012 10:38 AM by Stefan Wühl RSS

    How to Solve this? related to time

    Kabilan Kumarasamy

      Hi All,

       

      I have a issue.

       

      =Timestamp('11:12:55','HH:MM') // it will return 11:12

       

      But in my case, It is not give correct result.

       

      I have date column "EndTime" and I wrote the expression like =Timestamp(EndTime,'HH:MM') in text object. but it is not give a correct result.

       

      Wanted result is= 2:06

       

      Capture10.PNG

       

      Pls Help in this..

       

      Thanks In advance..

       

      Regards,

      Kabilan K.

        • Re: How to Solve this? related to time
          Stefan Wühl

          Try

           

          =Timestamp(EndTime,'hh:mm')

           

          to avoid showing the Month instead of minutes.

            • Re: How to Solve this? related to time
              Kabilan Kumarasamy

              Hi swuehl,

               

              Thank You ...

               

              Regards,

              Kabilan K.

              • Re: How to Solve this? related to time
                Kabilan Kumarasamy

                Hi All,

                 

                I have another one issue.

                 

                =Interval('00:45'-'00:44','mm') // It will return 01 ryt?

                 

                But for me it is not coming.

                 

                I have used like this.

                 

                Capture11.PNG

                 

                But it returns , 00.

                 

                I have written the script for StartTime and EndTime like this:

                 

                ,Timestamp(BaseDate,'hh:mm:ss') as EndTime

                           ,If(Key_Counter=1,Timestamp(BaseDate,'hh:mm:ss'),Previous(Timestamp(BaseDate,'hh:mm:ss'))) as StartTime

                 

                 

                Please Help me.

                 

                Thanks In advance.

                 

                Regards,

                Kabilan K.

                  • Re: How to Solve this? related to time
                    Stefan Wühl

                    Your end time is more precisely 00:45:03 and your start time 00:44:10, right?

                     

                    So using a format 'mm:ss', you should get 00:53 as result. And using only 'mm' as format code, you'll get 00

                     

                    In short, your numbers are not rounded.

                      • Re: How to Solve this? related to time
                        Kabilan Kumarasamy

                        Hi,,

                         

                        but want a minutes except seconds.

                         

                        That is y , I have changed like hh:mm

                         

                        Try

                         

                        =Timestamp(EndTime,'hh:mm')

                         

                        to avoid showing the Month instead of minutes.(your reply)

                         

                        So that I wrote a exprsssion like this,

                         

                        =interval(Timestamp(EndTime,'hh:mm')-Timestamp(startTime,'hh:mm'),'mm')

                         

                        here Timestamp(EndTime,'hh:mm') // it will return 00:45

                         

                        Timestamp(startTime,'hh:mm') // it will return 00:44

                         

                        So, I want a result as 01, but it is not coming.

                         

                        Please help me..

                        Thanks in advance

                         

                        Regards,

                        Kabilan K

                          • Re: How to Solve this? related to time
                            Stefan Wühl

                            Hi,,

                             

                            but want a minutes except seconds.

                             

                             

                            That's what you already have. You want the numbers rounded to the next minute (currently, it's just truncated).

                             

                            So maybe something like

                            =Interval( round('00:45:03'-'00:44:10',interval#('01','mm')) ,'mm')

                             

                            or

                            =Interval(round(EndTime-StartTime,interval#('01','mm')),'mm')

                             

                            Please note also, that using something like

                             

                            Timestamp(BaseDate,'hh:mm:ss') as EndTime

                             

                            in your load script is only formatting EndTime with given format code, it does not remove the date from its internal numerical representation. So if you subtract EndTime and StartTime, then format the result using interval(), your result will show quite large numbers if your original BaseDates are differing in date also.

                              • Re: How to Solve this? related to time
                                Kabilan Kumarasamy

                                Hi,

                                 

                                Now it is works. Thank u.

                                 

                                And please explain me Interval(round(EndTime-StartTime,interval#('01','mm')),'mm')

                                 

                                Here, interval#('01','mm'))  what ll it do?

                                 

                                Thank u so much..

                                 

                                Regards,

                                kabilan k.

                                  • Re: How to Solve this? related to time
                                    Stefan Wühl

                                    The date and time functions ending with # are to reading in or interpreting date and time values in QV, while the ones without # are formatting values. There are always pairs of them: date() / date#(), time() / time#(), interval#() / interval()

                                     

                                    So interval#('01','mm') is interpreting the text value 01 as minutes and stores the result as dual value, i.e. as a value that has a numeric part as well as a text part.

                                     

                                    You can also just use something like 1/24/60 instead, which will return the fraction of 1 minute compared to a day (24 hours a 60 minutes) and should be identical to the numerical part of above interval#() expression.

                                  • Re: How to Solve this? related to time
                                    Kabilan Kumarasamy

                                    Hi Swuehl,

                                     

                                    Here, I have problem.

                                     

                                    I worte a script like this.

                                     

                                    Interval(round(EndTime-StartTime,interval#('01','mm')),'mm')   // Your Script

                                     

                                    it is fine with this data: EndTime:23:04:59

                                                                     StartTime:23:03:45

                                    about this data it returns 1

                                     

                                    But it is not fine with this data: EndTime:23:04:59

                                                                                 StartTime:23:03:06

                                     

                                    about this data it returns 0

                                     

                                    But I want this also 1.

                                     

                                    so that I have changed the script like below.

                                     

                                    Interval(Ceil(EndTime-StartTime,interval#('01','mm')),'mm')

                                     

                                    but it returns also 1 for this data : EndTime:23:04:06

                                                                                      StartTime:23:04:08

                                     

                                    but here I want the value as 0

                                     

                                    Please help me.

                                     

                                    Thanks in advance.

                                     

                                    Regards,

                                    Kabilan K.


                                      • Re: How to Solve this? related to time
                                        Stefan Wühl

                                        Kabilan,

                                         

                                        I am not sure I understand your issue. Using your exact data:

                                         

                                        LOAD StartTime, EndTime

                                        ,interval(round(EndTime-StartTime,interval#('01','mm')),'mm') as DiffMinutesRounded

                                        ,interval(round(EndTime-StartTime,interval#('01','mm')),'ss') as DiffSecondsRounded

                                        ,interval(EndTime-StartTime,'ss') as DiffSeconds

                                        INLINE [

                                        StartTime, EndTime

                                        23:03:45,23:04:59

                                        23:03:06,23:04:59

                                        23:04:08,23:04:06

                                        ];

                                         

                                        I do get these results after reloading:

                                        StartTimeEndTimeDiffSecondsDiffSecondsRoundedDiffMinutesRounded
                                        23:03:0623:04:5911312002
                                        23:03:4523:04:59746001
                                        23:04:0823:04:06-2000

                                         

                                        Seems all reasonable to me if you want to round the intervals to the nearest minute.

                                         

                                         

                                        But it is not fine with this data: EndTime:23:04:59

                                                                                     StartTime:23:03:06

                                         

                                        about this data it returns 0

                                         

                                        But I want this also 1.

                                         

                                         

                                        I do get 02, not 0, please check above. And why do you want to display this interval as 1 minute (113 seconds are closer to 2 minutes).

                                         

                                        Regards,

                                        Stefan

                                          • Re: How to Solve this? related to time
                                            Kabilan Kumarasamy

                                            Hi,

                                             

                                            Sorry about my bad,

                                             

                                            my question is wrong.

                                             

                                            Here I corrected them.

                                             

                                            I worte a script like this.

                                             

                                            Interval(round(EndTime-StartTime,interval#('01','mm')),'mm')   // Your Script

                                             

                                            it is fine with this data: EndTime:23:04:59

                                                                             StartTime:23:03:45

                                            about this data it returns 1

                                             

                                            But it is not fine with this data: EndTime:23:04:10

                                                                                         StartTime:23:03:59

                                            now it ll return 11 sconds ryt? so rounded minutes is 0 (round(29seconds,1) it will return 0 and round(31seconds,1) it will return 1)

                                             

                                            But I want this also 1.

                                             

                                            so that I have changed the script like below.

                                             

                                            Interval(Ceil(EndTime-StartTime,interval#('01','mm')),'mm')// it will returns 1 bez difference is 2 seconds.

                                            (Ceil(29seconds,1) it will return 1 and round(31seconds,1) it will return 1)

                                             

                                            but it returns also 1 for this data : EndTime:23:04:08

                                                                                              StartTime:23:04:06

                                             

                                            but here I want the value as 0

                                             

                                            Please help me.

                                             

                                            Thanks in advance.

                                             

                                            Regards,

                                            Kabilan K.

                                              • Re: How to Solve this? related to time
                                                Stefan Wühl

                                                now it ll return 11 sconds ryt? so rounded minutes is 0 (round(29seconds,1) it will return 0 and round(31seconds,1) it will return 1)

                                                 

                                                But I want this also 1.

                                                 

                                                ...

                                                 

                                                but it returns also 1 for this data : EndTime:23:04:08

                                                                                                  StartTime:23:04:06

                                                 

                                                but here I want the value as 0

                                                 

                                                It's still unclear to me when you want to return 0 or 1. round() will return the neareast whole minute.

                                                If you don't want this, you need to specify what you want else.

                                                 

                                                Like for example:

                                                "I want to round to the nearest minute, except for a difference of exactely 11 seconds, where I want always 1 returned."

                                                 

                                                But I don't think that's what you want. Please specify.