Spent some fun time today trying to figure out where my miliseconds had gone. After querying a stream to create an infotable that includes the timestamp in DATETIME format with miliseconds, the resultant CSV file viewed in notepad++ does not have miliseconds in the standard time format. Anybody else have similar results, or am I missing something?
Service Output From Infotable: 2023-01-02 20:54:08.182
Directly Copied From CSV File: 2023-01-02 20:54:08
I ended up deriving timestamp over itself as a string and get the stream timestamp value as a string of total miliseconds. Reading the csv back into ThingWorx and adding stream entries accepts this miilseconds value. Just wanted to share in case others find themselves in a similar situation, or if there is a better way somebody has to share.
From the "writing csv" snip below, "t1" includes my desired miliseconds component of the DATETIME value, but it never makes its way to the csv file. "t2" has the timestamp in absolute miliseconds and the miliseconds component makes it way back to the stream entries after reading in the csv and creating the stream entries as shown in the "reading csv" snip.
-------------------------------
Snip Writing the CSV
-------------------------------
// query the stream and create an infotable
let query = {
"filters": {
"type": "LIKE",
"fieldName": queryFieldName,
"value": queryValue
}
};
let t1 = Things[streamName].QueryStreamEntriesWithData({
oldestFirst: true /* BOOLEAN */ ,
maxItems: 1000000 /* NUMBER {"defaultValue":500} */ ,
sourceTags: undefined /* TAGS */ ,
endDate: undefined /* DATETIME */ ,
query: query /* QUERY */ ,
source: undefined /* STRING */ ,
startDate: undefined /* DATETIME */ ,
tags: undefined /* TAGS */
});
// derive the timestamp field over itself to change to time in miliseconds as a String
// this is needed to keep miliseconds through the csv write-read process
// the stream will still end up with a timestamp of DATETIME datatype
let params = {
types: "STRING" /* STRING */ ,
t: t1 /* INFOTABLE */ ,
columns: "timestamp" /* STRING */ ,
expressions: "timestamp" /* STRING */
};
let t2 = Resources["InfoTableFunctions"].DeriveFields(params);
// write infotable to csv file
let params2 = {
path: "StreamData.csv" /* STRING */ ,
data: t2 /* INFOTABLE */ ,
fileRepository: "CSV.Repo" /* THINGNAME */ ,
withHeader: true /* BOOLEAN {"defaultValue":false} */
};
Resources["CSVParserFunctions"].WriteCSVFile(params2);
-------------------------------
Snip Reading the CSV
-------------------------------
let params = {
path: filename /* STRING {"defaultValue":"/"} */ ,
columnMappings: undefined /* STRING */ ,
hasHeader: true /* BOOLEAN {"defaultValue":false} */ ,
longitudeField: undefined /* NUMBER */ ,
dateFormat: undefined /* STRING */ ,
fileRepository: repoName /* THINGNAME */ ,
latitudeField: undefined /* NUMBER */ ,
fieldDelimiter: undefined /* STRING {"defaultValue":","} */ ,
stringDelimiter: undefined /* STRING {"defaultValue":"\""} */ ,
dataShape: streamCSV_DS /* DATASHAPENAME */
};
let t1 = Resources["CSVParserFunctions"].ReadCSVFile(params);
// for each row of the csv infotable, create a stream entry
t1.rows.toArray().forEach(row => {
// basic stream value fields
let source = row.source /* STRING */ ;
let sourceType = row.sourceType /* STRING */ ;
// the timestamp was converted to a string miliseconds value during export
// the stream timestamp (DATETIME) field here will accept the miliseconds string value
let timestamp = row.timestamp /* string mS value to DATETIME */ ;
let location = row.location;
// custom stream value fields from stream datashape
let valueInfotable = DataShapes[valueDataShape].CreateValues();
let valueFieldnames = valueInfotable.dataShape.fields;
let newOutputRow = new Object();
for (var fieldName in valueFieldnames) {
newOutputRow[fieldName] = row[fieldName];
}
valueInfotable.AddRow(newOutputRow);
// add stream entry
Things[streamName].AddStreamEntry({
sourceType: sourceType /* STRING */ ,
values: valueInfotable /* INFOTABLE */ ,
location: location /* LOCATION */ ,
source: source /* STRING */ ,
timestamp: timestamp /* DATETIME */ ,
tags: undefined /* TAGS */
});
});
