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

Community Tip - Need help navigating or using the PTC Community? Contact the community team. X

Delete DataTable entry by timestamp

Chao123
11-Garnet

Delete DataTable entry by timestamp

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.

1 ACCEPTED SOLUTION

Accepted Solutions

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.

View solution in original post

25 REPLIES 25
slangley
23-Emerald II
(To:Chao123)

Hi @Chao123.

 

Which version of ThingWorx are you running?

 

Regards.

 

--Sharon

Hello,

 

I believe it is 8.3.10-b1010

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"

}
}

 

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

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

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

Hello,

 

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

 

Thanks

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 

Try @adamtrainer's solution with this:

new Date(2020, 1, 30)

 / Constantine

Constantine,

 

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

 

Thanks

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

 

/ Constantine

Nope it will still return all entries no matter the timestamp

Chao123
11-Garnet
(To: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});

 

 

 

 

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

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;

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

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


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

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

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

It is a STRING currently. 

Chao123_0-1582821861525.pngChao123_1-1582821882454.png

This is my GetDate service.

 

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

Chao123_0-1582822101210.png

 

 

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

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.

@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?

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.

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

Top Tags