[Imported] Absolute date/time query format

[sbeadle]

>>Message imported from previous forum - Category:Scripts and Tips<<
User: JayB, originally posted: 2019-04-03 13:25:55 Id:393
I can write queries with a WHERE clause that uses the relative OPC date/time format (e.g. {OPC 'MO-1MO'}) and an absolute data format (e.g. {D '2019-03-11'}), but I can't figure out how to specify an exact date PLUS time format e.g. something like '2019-03-11 12:00:00'. What is that syntax? And is there a Help page somewhere that explains it, as I haven't found any examples online. Thanks.

Attached file: (editor/5n/jtdczfry5n5q.png), image.png File size: 1080

[sbeadle · Accepted solution]

>>Responses imported from previous forum

Reply From User: hardin4019, posted: 2019-04-03 20:20:11
I pulled up a historic list for a point and set the time range to "specific time" and then looked at the filter and saw ("~Time" BETWEEN TIMESTAMP '2019-04-03 01:00:00' AND TIMESTAMP '2019-04-04 01:00:00') so hopefully that helps.

Reply From User: JayB, posted: 2019-04-03 20:31:43
Great idea. I just tried exactly that syntax and it worked. Thanks for your help!

Reply From User: hardin4019, posted: 2019-04-03 22:15:26
No problem! Glad my method works!

Reply From User: jozzburn, posted: 2019-07-16 18:43:41
I'm doing the same. However, I want to put the query in the animation for the list and pull the between times from two internal time points. The working static query is

SELECT TOP( 1000 )
*
FROM
HISHOURLYAVERAGE
WHERE
( SUMMARY = 'Average' ) AND ( ID = 13027 ) AND ( "HISHOURLYAVERAGE"."Time" BETWEEN TIMESTAMP '2019-06-01 07:00:00' AND TIMESTAMP '2019-07-01 07:00:00' )
ORDER BY
"Id" ASC

The working animation generated for this is

'SELECT TOP( 1000 ) * FROM HISHOURLYAVERAGE WHERE ( SUMMARY = ''Average'' ) AND ( ID = 13027 ) AND ( "HISHOURLYAVERAGE"."Time" BETWEEN TIMESTAMP ''2019-06-01 07:00:00'' AND TIMESTAMP ''2019-07-01 07:00:00'' ) ORDER BY "Id" ASC'

How do i tell it to pull start from
"....~Config 1.Reports.~Date Parameters.Start.CurrentValue"

Tried replacing ''2019-06-01 07:00:00'' with "....~Config 1.Reports.~Date Parameters.Start.CurrentValue" and {OPC "....~Config 1.Reports.~Date Parameters.Start.CurrentValue"}

I think i'm close. Any ideas?

Reply From User: BevanWeiss, posted: 2019-07-16 23:45:03
This will get confusing with quotes..

So. You need to substitute the current static time (i.e. every BETWEEN the double single quotes) with a similarly FORMATted string. And of course because it's another string now, you'll need to 'add' it to the existing string.

i.e.
'SELECT blah blah ' +
'FROM blah blah ' +
'WHERE Time BETWEEN ' +
'TIMESTAMP ''' + FORMAT( TimeFormatRequired, "~Config.Reports.~Date Parameters.Start.CurrentValue") + '''' ' +
'AND TIMESTAMP ''' + FORMAT( TimeFormatRequired, "~Config.Reports.~Date Parameters.End.CurrentValue") + ''''

Reply From User: jozzburn, posted: 2019-07-17 16:40:56
Ahhh. Now i realize i needed to be closing the text with the ' , getting my values like i would normally do in an expression, then starting the text construction again. I was trying to get values from inside the ' '. Thanks for the help!