cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Did you get called away in the middle of writing a post? Don't worry you can find your unfinished post later in the Drafts section of your profile page. X

Export Data Widget with millisecond timestamp

mtyago
11-Garnet

Export Data Widget with millisecond timestamp

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

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

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()"

  });

 

View solution in original post

12 REPLIES 12
PaiChung
22-Sapphire I
(To:mtyago)

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
11-Garnet
(To:PaiChung)

I am using docker version(client and server on same machine) of Thingworx and I am not sure how to access the repository.
PaiChung
22-Sapphire I
(To:mtyago)

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
11-Garnet
(To:PaiChung)

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

 

 

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
5-Regular Member
(To:CarlesColl)

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. 

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
5-Regular Member
(To:CarlesColl)

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
22-Sapphire I
(To:fgrondin)

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
4-Participant
(To:CarlesColl)

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
5-Regular Member
(To:dpellerin)

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
22-Sapphire I
(To:fgrondin)

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

Top Tags