# Perspective - XYChart - Data from SQL - Auto requery periodically

**URL:** <https://forum.inductiveautomation.com/t/perspective-xychart-data-from-sql-auto-requery-periodically/55437>\
**Category:** Ignition\
**Tags:** ignition81\
**Created:** [January 27, 2022, 2:03pm UTC](https://forum.inductiveautomation.com/t/perspective-xychart-data-from-sql-auto-requery-periodically/55437 "2022-01-27T14:03:46Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![luc.martin](https://avatars.discourse-cdn.com/v4/letter/l/77aa72/32.png) [@luc.martin](https://forum.inductiveautomation.com/u/luc.martin)\
**Post date:** [January 27, 2022, 2:03pm UTC](https://forum.inductiveautomation.com/t/perspective-xychart-data-from-sql-auto-requery-periodically/55437/1 "2022-01-27T14:03:46Z")

</div>

I’m on 8.1.13

I search a trick to set an auto refresh with periodic interval.

---

<div class="post-metadata">

**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\
**Post date:** [January 27, 2022, 2:07pm UTC](https://forum.inductiveautomation.com/t/perspective-xychart-data-from-sql-auto-requery-periodically/55437/2 "2022-01-27T14:07:29Z")

</div>

Auto refresh of what? Perspective doesn’t require a refresh to see new data.

---

<div class="post-metadata">

**Author:** ![luc.martin](https://avatars.discourse-cdn.com/v4/letter/l/77aa72/32.png) [@luc.martin](https://forum.inductiveautomation.com/u/luc.martin)\
**Post date:** [January 27, 2022, 2:19pm UTC](https://forum.inductiveautomation.com/t/perspective-xychart-data-from-sql-auto-requery-periodically/55437/3 "2022-01-27T14:19:05Z")

</div>

I have an XYChart that I want to refresh data from an SQL database each minute

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [January 27, 2022, 2:36pm UTC](https://forum.inductiveautomation.com/t/perspective-xychart-data-from-sql-auto-requery-periodically/55437/4 "2022-01-27T14:36:02Z")

</div>

Set the _tag history binding_ End Date to `now(60000)`. This will cause a refresh every 60,000 ms.

---

<div class="post-metadata">

**Author:** ![luc.martin](https://avatars.discourse-cdn.com/v4/letter/l/77aa72/32.png) [@luc.martin](https://forum.inductiveautomation.com/u/luc.martin)\
**Post date:** [January 27, 2022, 2:42pm UTC](https://forum.inductiveautomation.com/t/perspective-xychart-data-from-sql-auto-requery-periodically/55437/5 "2022-01-27T14:42:11Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\
**Post date:** [January 27, 2022, 2:44pm UTC](https://forum.inductiveautomation.com/t/perspective-xychart-data-from-sql-auto-requery-periodically/55437/6 "2022-01-27T14:44:29Z")

</div>

There is a lot of information missing from your question. We are shooting a moving target. Can you edit your original question?

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [January 27, 2022, 2:49pm UTC](https://forum.inductiveautomation.com/t/perspective-xychart-data-from-sql-auto-requery-periodically/55437/7 "2022-01-27T14:49:25Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![luc.martin](https://avatars.discourse-cdn.com/v4/letter/l/77aa72/32.png) [@luc.martin](https://forum.inductiveautomation.com/u/luc.martin)\
**Post date:** [January 27, 2022, 2:57pm UTC](https://forum.inductiveautomation.com/t/perspective-xychart-data-from-sql-auto-requery-periodically/55437/8 "2022-01-27T14:57:48Z")

</div>

On dataSources of my XYChart I have a “data” object that is received their values from an SQL Query  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/2/e2eb14776e3c65037cb4f6e7883a756ca370f944.png)

I want that my “data” will be resfreshed each minute, so I want to do a refreshBinding(“props.dataSources.data”) each minute.

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [January 27, 2022, 2:59pm UTC](https://forum.inductiveautomation.com/t/perspective-xychart-data-from-sql-auto-requery-periodically/55437/9 "2022-01-27T14:59:02Z")

</div>

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`.

---

<div class="post-metadata">

**Author:** ![luc.martin](https://avatars.discourse-cdn.com/v4/letter/l/77aa72/32.png) [@luc.martin](https://forum.inductiveautomation.com/u/luc.martin)\
**Post date:** [January 27, 2022, 3:10pm UTC](https://forum.inductiveautomation.com/t/perspective-xychart-data-from-sql-auto-requery-periodically/55437/10 "2022-01-27T15:10:51Z")

</div>

```
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 data
```

---

<div class="post-metadata">

**Author:** ![victordcq](https://avatars.discourse-cdn.com/v4/letter/v/f6c823/32.png) [@victordcq](https://forum.inductiveautomation.com/u/victordcq)\
**Post date:** [January 27, 2022, 3:38pm UTC](https://forum.inductiveautomation.com/t/perspective-xychart-data-from-sql-auto-requery-periodically/55437/11 "2022-01-27T15:38:27Z")

</div>

What he ment was this  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/b/fb462c760246b6a4a3f0125a45ae74e86e2b3a9b.png)

> [@luc.martin](#):
>
> `rs = system.db.runQuery(sql,"data_series")`

While it may work now, you should not run queries like this but use system.db.[runPrepQuery](https://docs.inductiveautomation.com/display/DOC81/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  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/d/4d922be5ed854000ff5ecf04f3c884dc64af82bd.png)

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [January 27, 2022, 3:50pm UTC](https://forum.inductiveautomation.com/t/perspective-xychart-data-from-sql-auto-requery-periodically/55437/12 "2022-01-27T15:50:30Z")

</div>

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`.

---

<div class="post-metadata">

**Author:** ![luc.martin](https://avatars.discourse-cdn.com/v4/letter/l/77aa72/32.png) [@luc.martin](https://forum.inductiveautomation.com/u/luc.martin)\
**Post date:** [January 27, 2022, 3:56pm UTC](https://forum.inductiveautomation.com/t/perspective-xychart-data-from-sql-auto-requery-periodically/55437/13 "2022-01-27T15:56:23Z")

</div>

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”

---

<div class="post-metadata">

**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\
**Post date:** [January 27, 2022, 4:01pm UTC](https://forum.inductiveautomation.com/t/perspective-xychart-data-from-sql-auto-requery-periodically/55437/14 "2022-01-27T16:01:07Z")

</div>

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.