3 Replies Latest reply: Feb 10, 2017 5:02 PM by Stefan Wühl RSS

    Need Expression Help

    Mark Ritter

      I need to calculate the length of time between 2 date/timefields in my script

       

      Timestamp(Timestamp#([At Destination], 'M/D/YYYY h:mm') ) AS [At Destination],

      Timestamp(Timestamp#([Pickup Time], 'M/D/YYYY h:mm') ) AS [Pickup Time],

       

      These 2 fields are being loaded from an excel spread sheet in addition to others.

       

      I want to know the time lapse between the Pickup Time and At Destination. Basically how long it took them to leave and get to the site.

      I have tried a few things and nothing has worked so far.

        • Re: Need Expression Help
          Stefan Wühl

          Have you tried using Interval() function?

           

          LOAD *,

               Interval( [At Destination] - [Pickup Time] ) AS Interval;

          LOAD

               Timestamp(Timestamp#([At Destination], 'M/D/YYYY h:mm') ) AS [At Destination],

               Timestamp(Timestamp#([Pickup Time], 'M/D/YYYY h:mm') ) AS [Pickup Time],

               ...

           

          I am using a preceding LOAD to be able to use the correctly interpreted fields, you can also use something like

           

          Interval(

               Timestamp#([At Destination], 'M/D/YYYY h:mm') - Timestamp#([Pickup Time], 'M/D/YYYY h:mm')

               )  AS Interval