3 Replies Latest reply: Nov 17, 2011 2:33 PM by Kasper Larsen RSS

    i'm stuck..

    Kasper Larsen

      how do I convert this from a CSV  .. into a QV timestamp... while loading the CSV and converting it to local time..

       

      2011-11-17T13:12:27.654+00:00

      Notice the T between DATE and TIME and the UTC indicator (+00:00)

       

      I'm crosseyed with LOAD statments....

       

      thanks so much for helping!

        • i'm stuck..
          Mark Sheraton

          something like this should do it.

          Time(SubField( Replace('2011-11-17T13:12:27.654+00:00','+','T')     ,'T',2)     ,'hh:mm:ss')

          • i'm stuck..
            swuehl

            Are all your values UTC based?

             

            Then maybe like this:

            Let TZD= time(localtime()-localtime('GMT'));

             

            Input:

            LOAD * INLINE [

            Input

            2011-11-17T13:12:27.654+00:00

            ];

             

            LOAD Timestamp(timestamp#(left(replace(Input,'T',' '),index(Input,'+')-1),'YYYY-MM-DD hh:mm:ss.fff')+'$(TZD)') as result

            resident Input;

            • i'm stuck..
              Kasper Larsen

              ok - got the "simplest" solution. Just needed 2 x kick in the back..

               

              thanks

               

              Solution:

              ConvertToLocalTime(timestamp#(SubField(Replace([time (UTC)],'T',' '),'+',1),'YYYY-MM-DD hh:mm:ss[.fff]')) as TimeX

               

              (Time(UTC) is the field name) = to the engineers vision of a time stamp listed above. :-)