Duration text to Seconds

[pskumars]

HI ,

I have a list box duration values which was a string and i need to convert in seconds at

[henrikalmen]

It can be done in script, this will enable you to show the field duration as text or as a numeric value, where the value is the amount of seconds.

durationtable:

load dual(duration, durationSeconds) as duration;

load duration,

    (days * 24 * 60 * 60) + (hours * 60 * 60) + (minutes * 60) + (seconds) as durationSeconds

;

load duration,

    days,

    right(hours, len(hours)-index(hours, ' ', SubStringCount(hours, ' '))) as hours,

    right(minutes, len(minutes)-index(minutes, ' ', SubStringCount(minutes, ' '))) as minutes,

    right(seconds, len(seconds)-index(seconds, ' ', SubStringCount(seconds, ' '))) as seconds

;

load duration,

    if(index(duration, 'Day'), trim(subfield(duration, 'Day', 1)), 0) as days,

    if(index(duration, 'Hours'), trim(subfield(duration, 'Hours', 1)), 0) as hours,

    if(index(duration, 'Minutes'), trim(subfield(duration, 'Minutes', 1)), 0) as minutes,

    if(index(duration, 'Seconds'), trim(subfield(duration, 'Seconds', 1)), 0) as seconds

;

load * inline [

duration

2 Days 14 Hours 45 Minutes

21 Seconds

6 Hours 52 Minutes

1 Day 2 Hours 52 Minutes 3 Seconds

];

If you want separate text fields and numeric fields you can just remove the line "load dual(duration, durationSeconds) as duration;"

[ahaahaaha · Accepted solution]

Hi,

Try (look attached file)

Evaluate(Replace(Replace(Replace(Replace(Replace(Data, ' Days', '*86400'), ' Hours', '*3600'), ' Minutes', '*60'), ' Seconds', ''), ' ', '+'));

Result

Regards,

Andrey

[Kushal_Chawda]

Time:

LOAD *,

     interval(Seconds/86400,'hh:mm:ss') as Time;

LOAD *,

if(wildmatch(lower(SubField(Duration,' ',2)),'*year*'),SubField(Duration,' ',1)*31104000 +

SubField(Duration,' ',3)*2592000+SubField(Duration,' ',5)*86400+SubField(Duration,' ',7)*3600+

SubField(Duration,' ',9)*60+SubField(Duration,' ',11),

if(wildmatch(lower(SubField(Duration,' ',2)),'*month*'),

SubField(Duration,' ',1)*2592000+SubField(Duration,' ',3)*86400+SubField(Duration,' ',5)*3600+

SubField(Duration,' ',7)*60+SubField(Duration,' ',9),

if(wildmatch(lower(SubField(Duration,' ',2)),'*day*'),

SubField(Duration,' ',1)*86400+SubField(Duration,' ',3)*3600+

SubField(Duration,' ',5)*60+SubField(Duration,' ',7),

if(wildmatch(lower(SubField(Duration,' ',2)),'*hour*'),

SubField(Duration,' ',1)*3600+SubField(Duration,' ',3)*60+SubField(Duration,' ',5),

if(wildmatch(lower(SubField(Duration,' ',2)),'*minute*'),

SubField(Duration,' ',1)*60+SubField(Duration,' ',3),

if(wildmatch(lower(SubField(Duration,' ',2)),'*second*'),

SubField(Duration,' ',1))))))) as Seconds

Inline [

Duration

1 Year 11 Months 11 Days 1 Hour 11 Minutes 30 Seconds

2 Months 3 Days 1 Hour 9 Minutes 29 Seconds

3 Days 18 Hours 24 Minutes 35 Seconds

19 Minutes 39 Seconds

1 Day 18 Hours 19 Minutes 36 Seconds

30 Seconds

14 Hours 22 Minutes 38 Seconds ];

[MarcoWedel]

Hi,

another example: Re: Convert string to the timestamp

hope this helps

regards

Marco

[Kushal_Chawda]

pskumars When you have values like, 1 Day, 1 Month, 1 Minute, then I think this solution might not work. Also in future you will have the more data in which there might be possibility of getting strings like 1 Year as well.Please refer to my solution in that case.