# How to change the table in SQL

**URL:** <https://forum.inductiveautomation.com/t/how-to-change-the-table-in-sql/15639>\
**Category:** Ignition\
**Tags:** ignition79\
**Created:** [October 11, 2017, 4:31pm UTC](https://forum.inductiveautomation.com/t/how-to-change-the-table-in-sql/15639 "2017-10-11T16:31:21Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![lyhkch](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lyhkch/32/9381_2.png) [@lyhkch](https://forum.inductiveautomation.com/u/lyhkch)\
**Post date:** [October 11, 2017, 4:31pm UTC](https://forum.inductiveautomation.com/t/how-to-change-the-table-in-sql/15639/1 "2017-10-11T16:31:21Z")

</div>

ignition will Generate a table every month,like “sqlt\_data\_1\_2017\_10”,“sqlt\_data\_1\_2017\_09” , how to switch to a different table in SQL based on the time control,  
like this:  
SELECT dateadd(s, sqlt\_data\_1\_2017\_10.t\_stamp / 1000, ‘1970-01-01 08:00:00’) AS  
datetime,  
sqlt\_data\_1\_2017\_10.intvalue,  
sqlt\_data\_1\_2017\_10.tagid  
FROM sqlt\_data\_1\_2017\_10  
WHERE (sqlt\_data\_1\_2017\_10.t\_stamp \> ? ) AND (sqlt\_data\_1\_2017\_10.t\_stamp \< ( ?+1000) ) and  
sqlt\_data\_1\_2017\_10.tagid = 1

Now I want to change the table “sqlt\_data\_1\_2017\_10” according to time, how to do it ?

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [October 11, 2017, 4:57pm UTC](https://forum.inductiveautomation.com/t/how-to-change-the-table-in-sql/15639/2 "2017-10-11T16:57:32Z")

</div>

SQL doesn’t really allow you to select from dynamic table names. You’d have to modify the query before it’s run (possible with SQL Query Bindings, or through scripting), and how you’ll do that depends on exactly where you’re retrieving this data and how.

---

<div class="post-metadata">

**Author:** ![lyhkch](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lyhkch/32/9381_2.png) [@lyhkch](https://forum.inductiveautomation.com/u/lyhkch)\
**Post date:** [October 11, 2017, 5:13pm UTC](https://forum.inductiveautomation.com/t/how-to-change-the-table-in-sql/15639/3 "2017-10-11T17:13:30Z")

</div>

How can I use script to do this , Sorry , this is my first time use ignition

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [October 11, 2017, 5:16pm UTC](https://forum.inductiveautomation.com/t/how-to-change-the-table-in-sql/15639/4 "2017-10-11T17:16:23Z")

</div>

What exactly is the end goal? There may be a better option then manually querying the database for historical data. Such as the scripting function system.tag.queryTagHistory, or a Tag History Binding. These functions automatically manage the data partitions.

---

<div class="post-metadata">

**Author:** ![lyhkch](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lyhkch/32/9381_2.png) [@lyhkch](https://forum.inductiveautomation.com/u/lyhkch)\
**Post date:** [October 11, 2017, 5:22pm UTC](https://forum.inductiveautomation.com/t/how-to-change-the-table-in-sql/15639/5 "2017-10-11T17:22:53Z")

</div>

I want to use Report to view the history data

---

<div class="post-metadata">

**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\
**Post date:** [October 11, 2017, 5:26pm UTC](https://forum.inductiveautomation.com/t/how-to-change-the-table-in-sql/15639/6 "2017-10-11T17:26:18Z")

</div>

What you are looking for then is the [Tag Historian Query.](https://docs.inductiveautomation.com/display/DOC79/Tag+Historian+Query) Again, this will manage the data partitions for you.

---

<div class="post-metadata">

**Author:** ![lyhkch](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lyhkch/32/9381_2.png) [@lyhkch](https://forum.inductiveautomation.com/u/lyhkch)\
**Post date:** [October 11, 2017, 5:45pm UTC](https://forum.inductiveautomation.com/t/how-to-change-the-table-in-sql/15639/7 "2017-10-11T17:45:06Z")

</div>

Thanks, how can I binding start/end date, now the ‘t\_stamp’=1507728839065, How to convert 1507728839065 to a recognizable date type?

---

<div class="post-metadata">

**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\
**Post date:** [October 11, 2017, 7:05pm UTC](https://forum.inductiveautomation.com/t/how-to-change-the-table-in-sql/15639/8 "2017-10-11T19:05:45Z")

</div>

Within the tag history datasource you would refer back to report parameters:

 ![](https://us1.discourse-cdn.com/inductiveautomation/original/2X/3/3348333e98bd89591f940490b80e2ad9ddce6f91.png)

These would just be standard date objects - so if you’re using a Report Viewer on a window, you can simply add some Calendar/Date Range component(s) and bind the report’s StartDate and EndDate parameters to the date selector component.

---

<div class="post-metadata">

**Author:** ![cesrom](https://avatars.discourse-cdn.com/v4/letter/c/ea666f/32.png) [@cesrom](https://forum.inductiveautomation.com/u/cesrom)\
**Post date:** [October 13, 2017, 5:11pm UTC](https://forum.inductiveautomation.com/t/how-to-change-the-table-in-sql/15639/9 "2017-10-13T17:11:53Z")

</div>

I would agree that the tag\_history proposed by @paul-griffith, and @lrose is the way to go, that way you don’t have to bother manually querying the historical tables. But if you’ve decided you’d like to do it manually, I’ve done it in the past.

What I ended up doing to manually query historical information was to first query the **sqlth\_partitions** in order to determine how many tables I had to query from.

In my case I would be querying from not more than two **sql\_t\_data\_** tables at any given moment.

The query ended up as follows:

```auto
--MSSQLSERVER
DECLARE @tag_id int 
DECLARE @start_t_stamp bigint 
DECLARE @end_t_stamp bigint
DECLARE @sql varchar(MAX)
DECLARE @insert_statement varchar(50)

-- Set values for @tag_id, @start_t_stamp, @end_t_stamp

SELECT 
	@sql = COALESCE(@sql, '') + ('SELECT [tagid], [intvalue], [t_stamp] FROM [dbo].[' + a.[pname] + '] WHERE [intvalue] IS NOT NULL AND [tagid] = ' + CAST(@tag_id AS varchar) + ' AND [t_stamp] BETWEEN ' + CAST(@start_t_stamp AS varchar) + ' AND ' + CAST(@end_t_stamp AS varchar) + ' ' + CASE WHEN a.[num] != a.[max] THEN ' UNION ' ELSE '' END)
FROM 
	(
		SELECT 
			a.*, 
			ROW_NUMBER() OVER (ORDER BY A.[pname]) AS [num],
			COUNT(*) OVER () AS [max] 
		FROM 
		(
			SELECT 
				[pname]
			FROM [sqlth_partitions] 
			WHERE 
				@start_t_stamp BETWEEN [start_time] AND [end_time] 
			UNION 
			SELECT 
				[pname] 
			FROM [sqlth_partitions] 
			WHERE 
				@end_t_stamp BETWEEN [start_time] AND [end_time]
		) a
	) a

--Create TempTable
CREATE TABLE #Temp
(
	tagid int,
	intvalue int,
	t_stamp bigint
)

SET @insert_statement = 'INSERT INTO #Temp '
SET @sql = @insert_statement + @sql

EXEC(@sql)

-- Query #Temp table here

-- Finally drop #Temp table
IF(OBJECT_ID('tempdb..#Temp') IS NOT NULL)
BEGIN
	DROP TABLE #Temp
END

```

Hope it helps.

---

<div class="post-metadata">

**Author:** ![shwethag](https://avatars.discourse-cdn.com/v4/letter/s/e47774/32.png) [@shwethag](https://forum.inductiveautomation.com/u/shwethag)\
**Post date:** [February 15, 2018, 4:30pm UTC](https://forum.inductiveautomation.com/t/how-to-change-the-table-in-sql/15639/10 "2018-02-15T16:30:07Z")

</div>

I’m trying to do something similar. How can we bind the report’s Start Date and End Date parameters to the date selector component?

---

<div class="post-metadata">

**Author:** ![mr.davidmiller](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mr.davidmiller/32/65377_2.png) [@mr.davidmiller](https://forum.inductiveautomation.com/u/mr.davidmiller)\
**Post date:** [March 15, 2022, 6:07pm UTC](https://forum.inductiveautomation.com/t/how-to-change-the-table-in-sql/15639/11 "2022-03-15T18:07:26Z")

</div>

How to show the t\_stamp in a readable format would depend on your database.

- For MySQL you would select “FROM\_UNIXTIME(t\_stamp/1000)” rather than select “t\_stamp”
- For SQL Server you would select “SELECT DATEADD(s,t\_stamp/1000,‘1970-01-01 00:00:00’)”