Timestamp conversions

[#1]

Here's attached an example qvw that you can run on your machines.

It seems that the Timestamp only suports 17 characters and I need 23.

As how it is now it trims 6 characters (4 from the year, a dash and a month digit), but if I take off the milliseconds from the Date string (which consist in a point and 3 digits=4 characters) it only trims 6-4=2 characters from the Year.

InputTable:

LOAD * INLINE [

  MDate, Contract_ID,Attribute_Name,Attribute_Value_Text

  '2012-05-23 17:21:22.111', ball,color,red

  '2012-05-23 17:21:22.111', ball,diameter,10 cm

  '2012-05-23 17:21:22.111', ball,weight,100 g

  '2014-05-23 17:21:22.111', box,color,black

  '2014-05-23 17:21:22.111', box,height,16 cm

  '2014-05-23 17:21:22.111', box,length,20 cm

  '2014-05-23 17:21:22.111', box,weight,500 g

  '2014-05-23 17:21:22.111', box,width,10 cm

];

/* resulting table (delimiter=tab):

object color diameter weight height length width

ball red 10 cm 100 g

box black 500 g 16 cm 20 cm 10 cm

*/

GenTable:

Generic Load  timestamp(MDate,'YYYY-MM-DD HH:MM:SS.FFF') as SDate, Contract_ID, Attribute_Name, Attribute_Value_Text as AttributeValue Resident InputTable;

ResultTable:

LOAD Distinct timestamp(MDate,'YYYY-MM-DD HH:MM:SS.FFF') as SDate, Contract_ID Resident InputTable;

[martinpohl]

use timestamp(MDate,'YYYY-MM-DD hh:mm:ss.fff') instead of

[#3]

Didn't work. Have you tested on the attached qvw?

[Anonymous]

I don't see any problems in the attached file, except the one that Martin Pohl pointed out (M is for months, m is for minutes).  No any trimming...

Can you explain in some other way what the problem is?

[#5]

So on the plain table displayed on the example it shows the whole date?

Not on my machine.

[agilos_mla]

You have to ensure your settings for timestamp does not contain [] for millisecond, by default it is;

//SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';  

SET TimestampFormat='DD/MM/YYYY hh:mm:ss.fff';  

Then use timestamp#(MDate,'YYYY-MM-DD hh:mm:ss.fff') to interpret the date string correctly.

Michael

[#7 · Accepted solution]

Figured out. Just had to re-size manually the column by dragging and dropping.

I don't know why QV didn't auto adjust to the column size from the start!!! So much trouble because of a cosmetic thing!!!!!

[Anonymous]

Yes, it is correct:

2012-05-23 17:21:22.111

2014-05-23 17:21:22.111

I tried in QV 11:20 SR5 and in QV 8.50

The machine is Windows 7 64-bit

[#9]

Also have tried that before and didn't work. I already figured out the problem.

Thanks.

[#10]

Windows 7 64-bit here...