Export Data Widget with millisecond timestamp

[mtyago]

My mashup shows the table for valuestream, using queryPropertyHistory correctly, including the timestamp in millisecond format.

However, My problem is that when I export the table using the export widget, the resulting CSV file does not show the time stamp in milliseconds, the exported format is different from the mashup in this regard.

How can I export an infotable, which has timestamp in yyyy-MM-dd HH:mm:ss.SSS 

Thanks

[PaiChung]

One way you can do this is to create the payload server side and then use WriteCSV to write it to a file repository and then download the file to the client.

[mtyago]

I am using docker version(client and server on same machine) of Thingworx and I am not sure how to access the repository.

[PaiChung]

The System comes with the SystemRepository - FileRepository by default.

you can create any number of new ones with the FileRepository ThingTemplate

Each comes with ootb services to do: GetDirectoryStructure and GetFileListingWithLinks those can be used as rest calls, or even in composer test or even better build a FileRepository browser to see files in a Repository and Download the actual files.

Attaching a very simple one

[mtyago]

Thanks PaiChung for the solution, it is definitely helpful to have the browser.

[CarlesColl · Accepted solution]

Build a custom service and with Derive snippet build the desired result, for instance if you want unix timestamp:

var result = Resources["InfoTableFunctions"].DeriveFields({

   types: "LONG",

   t: originalInfoTable,

   columns: "timestamp",

   expressions: "myDateTimeField.getTime()"

  });

[fgrondin]

Hi,

Quick question, I have a result set coming from SQL all the timestamps (we have > 10 columns that are timestamps with ms).
I send the result of that into the Data export widget but as mentioned before, we are not getting millisecond in the file.

Doing DeriveFields creates new field base on x value. 

How can I just, with the same result from the result set, transform the date time type so we can have the ms in the file as opposed to create new columns?

I tried the service that creates a file and then you link the download file but it was missing some columns & value thus we don't want to use that.

PS: There is no datashape assign to the service and Columns have spaces. The service is "Dynamic" / Shapeless. 

[CarlesColl]

You can Derive with the same name as the original one, what I mean if if your DATETIME fieldName it's timestamp, you can derive and set timestamp as the resulting field and it will be overwitten.

[fgrondin]

Oh ok. 

If i understand correctly, the fieldname would be the column name. Would it work with spaces in the name though?
Will it create these new columns at the same place as they were before, or will it be at the end ? 

[PaiChung]

Not sure where the data goes afterwards, but if you are displaying this in a mashup, then I would take care of labels and order in the mashup itself.

[dpellerin]

My question is: where and why are we losing the milliseconds?

The solution you provide can indeed work. However people need to be aware that you lose the timezone part when using the getTime() method and it's unreadable to the human eye. So you need to rework it in the csv file to make it readable and with multiple columns and thousands of rows, it can be a pain.

The trick I found is basically to use an expression that would take into account the timezone difference and then I use the toISOString method to have it in a readable format.

var params = {
  types: "STRING",
  t: originalInfoTable,
  columns: "timestamp",
  expressions: "new Date(timestamp.getTime() - (timestamp.getTimezoneOffset() * 60000)).toISOString()"
};

The result is for each record is something like "2015-09-01T08:25:00.197Z". I could do a replace for the T and Z letters so that it's not confusing as it's not really an ISO format. One of the problems with this approach is the performance, for a report with lots of records I'm pretty sure it would take make the export duration significantly longer since it needs to perform the operation on each record.

Your help is really appreciated!

[fgrondin]

Thanks for the answer.

The question of why and where are we losing the millisecond is great.

If the issue is inside the widget then we know that the widget has to be modify to fix this problem. 

if the issue is not inside the widget but with the data itself, then we know we need to modify the data.

@CarlesColl, @PaiChung, do you guys happen to know why and where are we losing the millisecond ?

If so, could you explain please ?

[PaiChung]

I honestly am not sure why or where you might be losing the miliseconds