topic Re: convert to time ? in App Development

convert to time ?

gauravgg — Tue, 29 Nov 2016 05:02:17 GMT

Hi , i have the following field in to HH:MM:SS

example:---0 days 0Hr 0Min 14Sec into 00:00:14

0 days 0Hr 0Min 0Sec ->00:00:00

0 days 0Hr 0Min 36Sec->00:00:36

0 days 0Hr 0Min 0Sec

0 days 0Hr 0Min 19Sec

0 days 0Hr 1Min 42Sec

0 days 0Hr 2Min 14Sec

0 days 0Hr 0Min 29Sec

0 days 0Hr 1Min 45Sec

0 days 0Hr 0Min 0Sec

Thanks in Advance

Re: convert to time ?

gauravgg — Tue, 29 Nov 2016 05:21:43 GMT

i have to convert 0 days 0Hr 0Min 36Sec to 00:00:36

Re: convert to time ?

Vegar — Tue, 29 Nov 2016 05:32:35 GMT

I think the following will work

Interval(Interval#(Keepchar(yourField, '0123456789 '), 'D h m s'), 'hh:mm:ss')

Or this with double spaces between D and h

Interval(Interval#(Keepchar(yourField, '0123456789 '), 'D h m s'), 'hh:mm:ss')

Re: convert to time ?

Chanty4u — Tue, 29 Nov 2016 05:36:59 GMT

Am not sure, Not tested. but try this

LOAD * INLINE [

Hour

0 days 0Hr 0Min 0Sec

0 days 0Hr 0Min 19Sec

0 days 0Hr 1Min 42Sec

0 days 0Hr 2Min 14Sec

0 days 0Hr 0Min 29Sec

0 days 0Hr 1Min 45Sec

0 days 0Hr 0Min 0Se

]

Result;

LOAD * ,

time(timestamp#(Hour,'0 days' h '0hr' m '0Min' s '0sec'),'hhmmss') as new

Resident a;

Re: convert to time ?

Anonymous — Tue, 29 Nov 2016 06:46:33 GMT

I think this will work

Trim(KeepChar(Replace(Replace(Replace(your filedname,'0 days',''),'Hr',':'),'Min',':'),'01234567890:'))

Re: convert to time ?

gauravgg — Wed, 30 Nov 2016 04:49:18 GMT

thanks , this works

Interval(Interval#(Keepchar(yourField, '0123456789 '), 'D h m s'), 'hh:mm:ss')

Re: convert to time ?

gauravgg — Wed, 30 Nov 2016 09:51:26 GMT

Interval(Interval#(Keepchar(yourField, '0123456789 '), 'D h m s'), 'hh:mm:ss')

this is working but the negative values are showing positive

Re: convert to time ?

Vegar — Wed, 30 Nov 2016 15:29:47 GMT

Keepchar() removes all characters in your string except the ones in the second parameter.

If your negative numbers at annotated with minus, eg -2 , then you should add the - char to this parameter like this

Interval(Interval#(Keepchar(yourField, '-0123456789 '), 'D h m s'), 'hh:mm:ss')

Re: convert to time ?

gauravgg — Thu, 01 Dec 2016 04:38:06 GMT

if i apply this

Interval(Interval#(Keepchar(yourField, '-0123456789 '), 'D h m s'), 'hh:mm:ss')

0days 0Hrs 0Min -1Sec --> it is converting to -00:00:29 .......(it should show -00:00:01)

Re: convert to time ?

jonathandienst — Thu, 01 Dec 2016 04:58:45 GMT

The problem here is that the minus sign lands up in the middle of the interval expression and it looks like QV cannot handle that. This works correctly:

=Interval(Interval#(Keepchar('-0days 0Hrs 0Min 1Sec', '-0123456789 '), 'D h m s'), 'hh:mm:ss')

Can the sign appear on any of the quantities in this time string?

Re: convert to time ?

Vegar — Thu, 01 Dec 2016 07:24:46 GMT

If you could assume that all numbers are negative if you are able to find at least one minus sign in your string then you could try something like this.

If(FindOneOf(yourField,'-'),-1,1)*

Interval(Interval#(Keepchar(yourField, '0123456789 '), 'D h m s'), 'hh:mm:ss')

Cheers,

Vegar Lie Arntsen

Sent from my Oneplus using K-9 Mail. Please excuse my brevity.

Re: convert to time ?

Anonymous — Thu, 01 Dec 2016 08:06:11 GMT

Hi Gaurav,

PFA. hope this helps you.

Table:

LOAD *,

Time(TextBetween(Hour,'days ','Hr') & ':' & TextBetween(Hour,'Hr ','Min') & ':' & TextBetween(Hour,'Min ', 'Sec')) as Time;

LOAD * INLINE [

Hour

0 days 0Hr 0Min 0Sec

0 days 0Hr 0Min 19Sec

0 days 0Hr 1Min 42Sec

0 days 0Hr 2Min 14Sec

0 days 0Hr 0Min 29Sec

0 days 0Hr 1Min 45Sec

0 days 0Hr 0Min 0Sec

];