Welcome to the new Schneider Electric Community

It's your place to connect with experts and peers, get continuous support, and share knowledge.

  • Explore the new navigation for even easier access to your community.
  • Bookmark and use our new, easy-to-remember address (community.se.com).
  • Get ready for more content and an improved experience.

Contact SchneiderCommunity.Support@se.com if you have any questions.

Close
Invite a Co-worker
Send a co-worker an invite to the Exchange portal.Just enter their email address and we’ll connect them to register. After joining, they will belong to the same company.
Send Invite Cancel
84650members
353990posts

[Imported] Absolute date/time query format

EcoStruxure Geo SCADA Expert Forum

Find out how SCADA systems and networks, like EcoStruxure Geo SCADA Expert, help industrial organizations maintaining efficiency, processing data for smarter decision making with IoT, RTU and PLC devices.

Solved
sbeadle
Janeway Janeway
Janeway
0 Likes
1
1199

[Imported] Absolute date/time query format

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

Attachments

Accepted Solutions
sbeadle
Janeway Janeway
Janeway
0 Likes
0
1198

Re: [Imported] Absolute date/time query format

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

See Answer In Context

1 Reply 1
sbeadle
Janeway Janeway
Janeway
0 Likes
0
1199

Re: [Imported] Absolute date/time query format

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