11 Replies Latest reply: Dec 1, 2016 3:06 AM by Payal Shah

# convert to time ?

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

• ###### Re: convert to time ?

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

• ###### Re: convert to time ?

Am not sure, Not tested.  but try this

a:

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;

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

Resident a;

• ###### Re: convert to time ?

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 ?

thanks , this works

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

• ###### Re: convert to time ?

hi

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 ?

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 ?

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 ?

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 ?

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 ?

I think this will work

• ###### Re: convert to time ?

Hi Gaurav,

PFA. hope this helps you.

Table:

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

];