5 Replies Latest reply: Jun 28, 2017 3:11 AM by kushal chawda RSS

    Duration text to Seconds

    Surendra kumar

      HI ,

       

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

       

      listbox.JPG

        • Re: Duration text to Seconds
          Henrik Almén

          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;"

          • Re: Duration text to Seconds
            Andrey Khoronenko

            Hi,

             

            Try (look attached file)

             

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


            Result

            1.jpg


            Regards,

            Andrey

            • Re: Duration text to Seconds
              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 ];

               

               

              Capture.JPG

              • Re: Duration text to Seconds
                Marco Wedel

                Hi,

                 

                another example: Re: Convert string to the timestamp

                 

                hope this helps

                 

                regards

                 

                Marco