Knowledge Base

In HDS data storage, the TS field is optional and should not be used in a SQL query as it is not indexed. The Chrono field should be used instead.SolutionHere is an example of what not to do to retrieve the data between 9:00 and 10:00.

SELECT Chrono,Name,Value,Quality,TSFROM TRENDTABLE1 where TS > convert ( datetime , ‘2007-05-25 09:00:00’ , 121 ) and TS < convert ( datetime , ‘2007-05-25 10:00:00’ , 121 ) order by Chrono

The response time will be dependent of the size of the database (fast if <1GB, long if >2GB, time out if >4 GB).

Here is an example of how to do it properly.

Declare @LocalStartDate as datetime;
Declare @UTCStartDate as datetime;
Declare @LocalEndDate as datetime;
Declare @UTCEndDate as datetime;

SET @LocalStartDate = ‘2022-05-05 19:24:00’;
SET @UTCStartDate = @LocalStartDate at time zone ‘Central Europe Standard Time’ at time zone ‘UTC’;
SET @LocalEndDate = ‘2022-05-05 19:40:00’;
SET @UTCEndDate = @LocalEndDate at time zone ‘Central Europe Standard Time’ at time zone ‘UTC’;

SELECT Chrono,Name,Value,Quality,TS FROM TRENDTABLE1
where Chrono >= (cast( DATEDIFF(second,convert(datetime, ‘1970-01-01 00:00:00’,121) ,convert(datetime, @UTCStartDate,121))as bigint)* 10000000)+ 116444736000000000
and Chrono <= (cast( DATEDIFF(second,convert(datetime, ‘1970-01-01 00:00:00’,121) ,convert(datetime, @UTCEndDate,121))as bigint)* 10000000)+ 116444736000000000 order by Chrono

This looks more complex but the query uses the native indexes of the tables. The result of the request is therefore almost independent of the size of the database.

See also KB article KB479 to know more about How to generate TS column if it is absent from the database.

Finally it can be gathered all together like that:

Declare @LocalStartDate as datetime;
Declare @UTCStartDate as datetime;
Declare @LocalEndDate as datetime;
Declare @UTCEndDate as datetime;

SET @LocalStartDate = ‘2022-05-05 19:24:00’;
SET @UTCStartDate = @LocalStartDate at time zone ‘Central Europe Standard Time’ at time zone ‘UTC’;
SET @LocalEndDate = ‘2022-05-05 19:40:00’;
SET @UTCEndDate = @LocalEndDate at time zone ‘Central Europe Standard Time’ at time zone ‘UTC’;

SELECT CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, DATEADD(second,((chrono-116444736000000000)/10000000), convert(datetime,’1970-01-01 00:00:00′,121))),DATENAME(TzOffset, SYSDATETIMEOFFSET()))) as LocalTSCalculated,Name,Value,Quality FROM TRENDTABLE1
where Chrono >= (cast( DATEDIFF(second,convert(datetime, ‘1970-01-01 00:00:00’,121) ,convert(datetime, @UTCStartDate,121))as bigint)* 10000000)+ 116444736000000000
and Chrono <= (cast( DATEDIFF(second,convert(datetime, ‘1970-01-01 00:00:00’,121) ,convert(datetime, @UTCEndDate,121))as bigint)* 10000000)+ 116444736000000000 order by Chrono