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