Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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. 🙂
something like this should do it.
Time(SubField( Replace('2011-11-17T13:12:27.654+00:00','+','T') ,'T',2) ,'hh:mm:ss')
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;
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. 🙂