How to Solve this? related to time

[#1]

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

Pls Help in this..

Thanks In advance..

Regards,

Kabilan K.

[swuehl]

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:

StartTime	EndTime	DiffSeconds	DiffSecondsRounded	DiffMinutesRounded
23:03:06	23:04:59	113	120	02
23:03:45	23:04:59	74	60	01
23:04:08	23:04:06	-2	0	00

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

[#3]

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.

[swuehl]

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.