How to change the table in SQL

Ignition
1

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 ?

2

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.

3

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

4

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.

1 Like
5

I want to use Report to view the history data

6

What you are looking for then is the Tag Historian Query. Again, this will manage the data partitions for you.

7

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

8

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

image.png788×927 22.8 KB

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.

9

I would agree that the tag_history proposed by @PGriffith, 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:

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

1 Like
10

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?

11

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’)”