Delete DataTable entry by timestamp

[Chao123]

How might I go about deleting all items in a datatable by timestamp? Say over a year old? I am not seeing timestamp as a parameter of the DeleteDataTableEntriesWithQuery snippet.

[slangley]

Hi @Chao123.

Which version of ThingWorx are you running?

Regards.

--Sharon

[Chao123]

Hello,

I believe it is 8.3.10-b1010

[Ciprian-Traian]

You can use a query ( inside the service) which will delete all data previous to the timestamp defined in value :

{

"filters": {

"type": "LT",

"fieldName": "timestamp",

"value": "2020-01-30"

}
}

[Chao123]

Thank you for your suggestion. I am not doing this correctly obviously.

var query = {
"filters":{
"type": "LT",
"fieldName": "timestamp",
"values": "2020-01-30"
}
};

var result = me.DeleteDataTableEntriesWithQuery(query);

This will still delete all of my entries even if they are not LT the value. Not sure what I am doing wrong.

[adamtrainer]

DeleteDataTableEntriesWithQuery is expecting a numeric value that represents milliseconds since epoch:

var query = {
 "filters": {
   "type": "LT",
   "fieldName": "timestamp",
   "value": new Date("2020-01-30")
 }
};

[Chao123]

Hello,

Thank you for looking into this. This still is clearing every entry in the data table.

Thanks

[Constantine]

I wonder if date format string can be specific to your server locale... How about this?

var query = {
 "filters": {
   "type": "LT",
   "fieldName": "timestamp",
   "value": new Date(2020, 1, 30)
 }
};

/ Constantine 

[Constantine]

Try @adamtrainer's solution with this:

new Date(2020, 1, 30)

 / Constantine

[Chao123]

Constantine,

I tried this as well. Same issue exists. Very strange.

Thanks

[Constantine]

@Chao123, does QueryDataTableEntries work as expected with the same query?

/ Constantine

[Chao123]

Nope it will still return all entries no matter the timestamp

[Chao123]

var query = {
  "filters": {
  "type": "LT",
  "fieldName": "timestamp",
  "value": new Date("2020-01-30")
}
};

var values = me.CreateValues();

  values.DailySheetCount = Things["MBCI.JAIN.Thing.SheetCount.NW1"].TotalDailySheetCount; //INTEGER
  values.HrSheetCount = Things["MBCI.JAIN.Thing.SheetCount.NW1"].SheetCount; //INTEGER
  values.key = me.key; //INTEGER [Primary Key]
  values.Date = undefined;



var params = {
  query: query /* QUERY */,
  values: values /* INFOTABLE*/,
};

var result = me.DeleteDataTableEntriesWithQuery({params:params});

[Constantine]

And the timestamp column is correct in the result? Are you sure you initialize it correctly when you insert data into this data table?

[Chao123]

I believe I am. This is my code for entering data.

HrSheetCount = me.SheetCount;
Time = me.GetTime();
Date = me.GetDate();
me.TotalDailySheetCount = me.SheetCount + me.TotalDailySheetCount;
DailySheetCount = me.TotalDailySheetCount;

key = Things["MBCI.JAIN.DataTable.SheetCount"].key;



var params = {
  infoTableName : "IT",
  dataShapeName : "MBCI.JAIN.DataShape.SheetCounter.DailyCounts"
};

// CreateInfoTableFromDataShape(infoTableName:STRING("InfoTable"), dataShapeName:STRING):INFOTABLE(MBCI.JAIN.DataShape.SheetCounter.DailyCounts)
var IT = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape(params);

IT.AddRow({HrSheetCount: HrSheetCount, DailySheetCount: DailySheetCount, Date: Date, Time: Time, key: key});



var values = Things["MBCI.JAIN.DataTable.SheetCount"].CreateValues();

values.key = Things["MBCI.JAIN.DataTable.SheetCount"].key; //INTEGER [Primary Key]
values.HrSheetCount = HrSheetCount; 
values.DailySheetCount = DailySheetCount;
values.Date = Date;
values.Time = Time;


var params1 = {
  sourceType: undefined /* STRING */,
  values: values /* INFOTABLE*/,
  location: undefined /* LOCATION */,
  source: undefined /* STRING */,
  tags: undefined /* TAGS */
};
Things["MBCI.JAIN.DataTable.SheetCount"].key = values.key+1;
// result: STRING
var id = Things["MBCI.JAIN.DataTable.SheetCount"].AddDataTableEntry(params1);

me.ResetSheetCount();
result = IT;

[Constantine]

First of all, let me simplify this code by removing unnecessary stuff:

var dataTable = Things["MBCI.JAIN.DataTable.SheetCount"];

var result = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape({ 
	dataShapeName : "MBCI.JAIN.DataShape.SheetCounter.DailyCounts" 
});
result.AddRow({
	HrSheetCount: me.SheetCount, 
	DailySheetCount: me.TotalDailySheetCount, 
	Date: me.GetDate(), 
	Time: me.GetTime(), 
	key: dataTable.key
});

dataTable.AddDataTableEntry({ values: result });
dataTable.key++;
me.ResetSheetCount();

I didn't test it, but unless I'm missing something it should produce exactly the same result as the code you mentioned above.

It's easy to see that the timestamp that you use for filtering contains the time when you call this AddDataTableEntry ("current time" sort of say) -- is that what you expect? Or do you want to filter on those "Date" and "Time" fields instead?

/ Constantine

[Chao123]

Much cleaner and produces the same result, awesome thank you.

I believe the timestamp entry is inevitable and occurs automatically correct? I think it would be easier to filter just by the 'Date' column. I tried that same query mentioned above using "Date" as the fieldname and "value": new Date(01/30/2020) and ("01/30/2020")

[Constantine]

---

I believe the timestamp entry is inevitable and occurs automatically correct?

---

Yes, that's correct.

The query syntax that @adamtrainer mentioned should also work for your "Date" field, as soon as it has DateTime data type. If it's a simple STRING, then you would need to format it differently (e.g. "20200130" or "2020/01/30") so that your dates can be ordered lexicographically, to make it work with "LT" / "GT" query types (I'm not even sure LT/GT work with strings).

/ Constantine

[Chao123]

I am not thinking that LT/GT does as I tried those two different formats and all the data was cleared.

[Constantine]

So what is your "Date" -- is it a STRING or DATETIME? If it's a STRING, can you provide few examples of what exactly is inside? In general, it would help if you post a screenshot of what you see when you execute QueryDataTableEntries().

[Chao123]

It is a STRING currently. 

This is my GetDate service.

Result after I run the service 'StoreHourlySheetCount' that you cleaned up for me.

[Constantine]

Can you add some DateTime field (of type DATETIME, obviously) next to your Date and Time? This will make your life much simpler. Two side notes:

1. You should probably use Stream instead of DataTable here. In most cases it provides better performance, and allows you to handle timestamps (you can specify them at the insert moment). It also takes care of generating unique IDs, so that you don't need to use the error-prone "dataTable.key++" approach.
2. In 90% of cases you shouldn't store Date/Time as strings, unless you're absolutely sure that all your users are in the same time zone. There are some exceptions to this rule, but I haven't seen many, and most probably it's not your case either. So instead of strings you'd better off storing DATETIME values.

/ Constantine

[Constantine]

Wait a second, I've just realized something... You're storing two properties of that "MBCI.JAIN.Thing.SheetCount.NW1" thing -- why don't you just make them logged and use ValueStream instead? Then you can just get rid of all your code altogether.

[Chao123]

@Constantine all users will be on the same time zone so I won't have to worry about that issue. One question on logging these properties. Currently, at each hour (using several schedulers) I am executing the storehourlysheetcount property that takes that last hours sheet count adds it to the totaldailysheetcounts stores it in the datatable, clears it, and puts it in the totaldailysheetcount property. If I make these logged and go the valuesteam route is this still possible?

[Constantine · Accepted solution]

Well, just create a ValueStream object, assign it to your thing (or thing template), make your totaldailysheetcount logged and see how it works -- it's really that simple, just execute GetPropertyHistory() on that thing again to see changes history. Check out Help on Value Streams -- it has all technical details.

[Chao123]

I appreciate all of your help. Thank you for your time and suggestions @Constantine