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

Delete DataTable entry by timestamp

SOLVED
Highlighted
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
Highlighted

Re: Delete DataTable entry by timestamp

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
Highlighted

Re: Delete DataTable entry by timestamp

Hi @Chao123.

 

Which version of ThingWorx are you running?

 

Regards.

 

--Sharon

Highlighted

Re: Delete DataTable entry by timestamp

Hello,

 

I believe it is 8.3.10-b1010

Highlighted

Re: Delete DataTable entry by timestamp

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"

}
}

 

Highlighted

Re: Delete DataTable entry by timestamp

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.

Highlighted

Re: Delete DataTable entry by timestamp

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

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

Re: Delete DataTable entry by timestamp

Hello,

 

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

 

Thanks

Highlighted

Re: Delete DataTable entry by timestamp

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 

Highlighted

Re: Delete DataTable entry by timestamp

Try @adamtrainer's solution with this:

new Date(2020, 1, 30)

 / Constantine

Highlighted

Re: Delete DataTable entry by timestamp

Constantine,

 

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

 

Thanks

Highlighted

Re: Delete DataTable entry by timestamp

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

 

/ Constantine

Highlighted

Re: Delete DataTable entry by timestamp

Nope it will still return all entries no matter the timestamp

Highlighted

Re: Delete DataTable entry by timestamp

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});

 

 

 

 

Highlighted

Re: Delete DataTable entry by timestamp

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

Highlighted

Re: Delete DataTable entry by timestamp

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;
Highlighted

Re: Delete DataTable entry by timestamp

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

Highlighted

Re: Delete DataTable entry by timestamp

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

Highlighted

Re: Delete DataTable entry by timestamp


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

Highlighted

Re: Delete DataTable entry by timestamp

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

Highlighted

Re: Delete DataTable entry by timestamp

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

Highlighted

Re: Delete DataTable entry by timestamp

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

 

 

Highlighted

Re: Delete DataTable entry by timestamp

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

Highlighted

Re: Delete DataTable entry by timestamp

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.

Highlighted

Re: Delete DataTable entry by timestamp

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

Highlighted

Re: Delete DataTable entry by timestamp

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

Highlighted

Re: Delete DataTable entry by timestamp

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

Announcements

Thingworx Navigate content has a new home! Click here to access the new Thingworx Navigate forum!