Start and End data values

Ignition
,
1

Objective: To display the volume used based on an Easy Charts StartDate and EndDate.

So far I've figured out how to get the charts start and end dates, pass that to a table query and display a listing of all the data points. This is a lot of extra data, as I only need to know the tag's value at the start and end of the time selected on the Easy Chart. I've found a few similar questions on the forum, but none of them look to exclude data. Reading Reading and Writing to Tags - Ignition User Manual 8.0 - Ignition Documentation in the docs, but it will return the values, quality, and timestamp; I want to supply the timestamp to read the value at.

I'm still really new to this, so maybe I'm just not aware of something simple. I think I could get this solved, but at the cost of a lot of unnecessary queries.

Clarification: I only need the volume at the StartDate and at the EndDate, to calculate the difference. The data in between is useless for this purpose, as the level can change due to outside factors.

2

Iā€™m not for sure exactly what youā€™re looking for. You state you want to display the volume used between the StartDate and EndDate. To me this sounds like you want to aggregate the values.

If thatā€™s the case, then it should be as simple as using and aggregate function in the query.

Other solutions, assuming your using the tag historian, would be to use a Tag History Binding with an aggregation mode or in script use system.tag.queryTagCalculations.

1 Like
3

Sorry I wasnā€™t clearer, I want to know the values at the Start and the End of the chart, which will be subtracted to determine volume lost to the process. The values in between Start and End can fluctuate as product is pumped between vessels during the process. I only need to know the actual quantity at Start and at End, in between is not necessary. Iā€™ll look further into the Tag History Binding.

4

If you are pulling from tag history, youā€™ll use the Range aggregation in system.tag.queryTagCalculations. If you are using data from a regular DB table, then it would be something like this,

SELECT max(volume) - min(volume)
FROM myTable
where t_stamp between 'yourStartDate' and 'yourEndDate'
5

I tried this, with modifications to suit my particulars, but getting a conversion error on the date format, looking into this.

6

Post what youā€™ve tried

7

Wondering if the tag historian would work with a point count of 2

8

SELECT max(ā€œWork Tank 1 Imperial Gallonsā€) - min(ā€œWork Tank 1 Imperial Gallonsā€)
FROM group_table
WHERE t_stamp between ā€˜StartDateā€™ and ā€˜EndDateā€™

9

I wondered similarly, if I just used StartDate and EndDate, and an aggregation mode.

10

You're supplying the literal text "StartDate" here instead of the actual start date and end dates. You need to use the property selector to select these properties from your easy chart. You should end up with something like {Root Container.EasyChart.StartRange} or similar

1 Like
11

Iā€™ve made some changes, I think if I do two separate queries to get the values, then use my expression to get the difference, it will be less traffic.

Trying to get the value of the tag at the startdate of the chart using:

SELECT {[default]Work Tank 1/Work Tank 1 Volume Imperial Gallons.value}
FROM charge_reports
WHERE t_stamp = {Root Container.Easy Chart.startDate}

but getting an error:

Exception: Error running query:
SQLQuery(query=SELECT 31990.16
FROM charge_reports
WHERE t_stamp = 2021-02-02 08:00:00.000, database=Charge_Reports)@0ms
On: Charts Testing.Root Container.Starting Volume.value
caused by GatewayException: Incorrect syntax near ā€˜08ā€™.
caused by SQLServerException: Incorrect syntax near ā€˜08ā€™.

Do I need to format the returned value somehow?

12

That is a tag path you are trying to select.

Generally, you select from columns in a DB table.

I think you need to sketch out on paper what you are trying to achieve (post it here).

It seems you are going around the world a bit to try and get this done, between Historian, a table, tag paths, and then an expression.

13

This is what I'm trying to accomplish. I think the reason it's confusing is that there is probably a few different ways to go about it. I think the easiest would be to use the chart's Start and End dates to query the DB for the values at those times, then use my expression to calculate the value. Alternatively, if I could do a query and then just return the first and last values (perhaps in two separate queries?) would also work.

14

First thing.
Where is your volume values stored? In a regular database table, or are you using tag history to log the volume. It seems that you are using tag history therefore you should use something like this (used in the easy charts propertyChange extension function)

if event.propertyName == 'startDate':
    startDate = event.source.startDate
    endDate = event.source.endDate
    tagPath= '[default]Work Tank 1/Work Tank 1 Volume Imperial Gallons'

    history = system.tag.queryTagHistory(paths= [tagPath],startDate=startDate,endDate=endDate,returnSize=1,aggregationMode='Range')

    event.source.parent.getComponent('Power Table').data = history
1 Like
15

You are correct, I am using Tag History, to work with the Easy Chart.

This thread Read a historical tag value is very similar to what Iā€™m trying to do. I can get the dates for the start and end of the chart, but Iā€™m not having much luck getting the historical value of the tag at those dates.

I really appreciate everyoneā€™s efforts here!

16

Sorry, you still need the single quotes around the date, so ā€˜{Rootā€¦}ā€™

17

The code I gave you will give you what you asked for. No need to get the value at start and end then subtract manually. The Range aggregation will do that for you.

If you want just the value and not the dataset returned from queryTagHistory() then just add

print history.getValueAt(0,1)

(I think that would be the right index for the value)

18

Sorry, but this is the same as Chart > Scripting > Extension Functions > configureChart ?

Does the .getValueAt(0,1) reference the data by (row,column)? I can't seem to get it to print that cell.

19

I said extension function, but I meant event handler propertyChange. I recommend placing a temporary power table on your window so you can see the dataset that tagHistory is returning (my original script shows this). Then once you verify that the info is correct you can index the row value you want and assign the result where ever you want.

Yes that is correct. Note that print displays output to the Output Console under Tools>Console or ctrl + shift + C.

Also, the script I wrote will only fire when the start date of the chart changes, but this can be changed to other properties.

if event.propertyName == 'startDate':
1 Like
20

Or to include multiple properties, like so:

if event.propertyName in ('startDate', 'endDate'):
2 Likes