Knowledge Base

TS is an optional column. Normally you must select the option in the HDS configuration to have it in the database.

However, it is also possible to use the below formula to recalculate it on the fly. This type of query is a little more expensive in terms of CPU, and complicates the request, but saves space on each record.

Select *, DATEADD ( second ,((CHRONO-116444736000000000)/ 10000000),convert(datetime, ‘1970-01-01 00:00:00’,121)) as TSCalculated FROM [TRENDTABLE1] order by Chrono

It is also possible to have the TS column as local time using this query.

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] order by Chrono

See also KB article KB20 to know more about Use of Chrono time-stamping to make SQL queries