I’m on 8.1.13
I search a trick to set an auto refresh with periodic interval.
Auto refresh of what? Perspective doesn’t require a refresh to see new data.
I have an XYChart that I want to refresh data from an SQL database each minute
Set the tag history binding End Date to now(60000). This will cause a refresh every 60,000 ms.
My data is not binded to a tag, I use an SQL database that is used to store values that is coming from various sources.
I want to be able to requery each minute.
There is a lot of information missing from your question. We are shooting a moving target. Can you edit your original question?
The best way to get use out of this forum is to show exactly how you have your system setup with regards to the problem. Whether that is scripts that are generating the data for your chart, or you have a named query binding, etc. There are many ways to do the same exact thing in Ignition so context is key. We need to see the whole sequence of steps where there is a bug so we can better troubleshoot.
On dataSources of my XYChart I have a “data” object that is received their values from an SQL Query
I want that my “data” will be resfreshed each minute, so I want to do a refreshBinding(“props.dataSources.data”) each minute.
Looks like your data is binded. Click that blue chain graphic to show us the expression. You can set polling there directly if its a query and then no need for an extra script to do refreshBinding.
from datetime import datetime, timedelta
def calcDate(original_date):
my_date = None
try:
if "min" in original_date.lower():
my_date = datetime.now() - timedelta(minutes = int(original_date.lower().replace("min","").strip()))
elif "h" in original_date.lower():
my_date = datetime.now() - timedelta(hours = int(original_date.lower().replace("h","").strip()))
elif "d" in original_date.lower() or "j" in original_date.lower():
my_date = datetime.now() - timedelta(days = int(original_date.lower().replace("d","").replace("j","").strip()))
elif "m" in original_date.lower():
my_date = _Functions.monthdelta(datetime.now(), -int(original_date.lower().replace("m","").strip()))
elif ("y" in original_date.lower() or "a" in original_date.lower()) and len(myDate) < 8:
d = datetime.now()
my_date = d.replace(year = d.year - int(original_date.lower().replace("y","").replace("a","").strip()))
else:
my_date = original_date
except:
pass
if my_date is None:
my_date = original_date
return my_date
self.props.dataSources.data = []
if not value["data_series_id"] > 0:
return []
sql = "SELECT * FROM tbl_numeric_entries WHERE data_series_id=" + str(value["data_series_id"])
if not value["date_from"] is None and not value["date_from"] == "":
new_date = calcDate(value["date_from"])
if "datetime" in str(type(new_date)):
sql += " AND timestamp_sampled>='" + str(new_date) + "'"
else:
sql += " AND timestamp_sampled>='" + str(datetime.strptime(new_date, '%Y-%m-%d %H:%M:%S' if 'fr' in self.session.props.locale else '%m-%d-%Y %H:%M:%S')) + "'"
if not value["date_to"] is None and not value["date_to"] == "":
new_date = calcDate(value["date_to"])
sql += " AND timestamp_sampled<='" + str(datetime.strptime(new_date, '%Y-%m-%d %H:%M:%S' if 'fr' in self.session.props.locale else '%m-%d-%Y %H:%M:%S')) + "'"
rs = system.db.runQuery(sql,"data_series")
data = []
if len(rs):
headers = system.dataset.getColumnHeaders(rs)
for row in rs:
my_dict = {}
for col in headers:
my_dict[col] = row[col]
data.append(my_dict)
source = {}
return dataWhat he ment was this
While it may work now, you should not run queries like this but use system.db.runPrepQuery
Also it seems you are doing a lot of work formating the date...
...
Anyways a simple expression function on a new custom prop should be able to refreshBindings.
But i suggest you make a cleaner query first xd
2 Likes
Another side note but use system.date functions as they are java.util.Date and will work with system.db.runPrepQuery automatically. Then you will not have to worry about formatting dates for your SQL query which I find a big plus. I am not sure that system.db.runPrepQuery will work with datetime.datetime.
I do lot of work for formating date because my inputbox can be a datetime or a formula, user can type “2d” then the date is calcuted as today - 2 days, the field accept “y,m,d,h and min”
If you’re able to put that logic into some expression using the dateFormat() or similar, those get outputted as java.util.Date as well and are also immediately usable for system.db.runPrepQuery. You would use a ? in your query where you need the date, no formatting on your part required. I know this isn’t the main point of your question but personally I hate having to manually handle and touch dates so I avoid it whenever possible.