i create a transaction with timestamp(with second) execution every 1min, now i want to create query tag to retrieve data from a exact time(yyyy-mm-dd hh:mm)no second. and this datetime can be selected by a calendar or pop calendar. i have no idea, could somebody help me on this?
So you’re going to want to play around with an SQL Select statement with a WHERE clause that limits the results to that minute. For example
SELECT * FROM [YOUR_TABLE_NAME] WHERE [Column for the tagname] = 'tagname' AND [column for the date] = 'the time you want to look up'SELECT * will get data from every row of the table, you can modify this to SELECT column1, column2, etc. Where column1 and column2 are the actual names of columns in your table. Since you’ve done this as a transaction you should have defined column names for each tag already.
WHERE will limit it to just the rows that meet a specific criteria (in this case a specific tagname and date)
note that the actual tag name is in single quotes. It may not be strictly necessary for the version of SQL you are using to use single quotes, but for me it’s force of habit. The quote marks tell it that you’re giving it a literal string to match.
For the time you want to look up it should be something like ‘2017-03-29 9:25:00’ (again note the single quotes) even though you don’t put seconds, SQL most likely will so you may get better results doing:
WHERE [Column for the tagname] = 'tagname' AND [column for the date] >= '2017-03-29 9:25:00' AND [column for the date] < '2017-03-29 9:26:00' the modified WHERE clause will give you any result for minute of 9:25 AM just in case it gets time stamped at the 4th or 30th or whatever second and not exactly at the 0th second of the minute.
Hi Abishur, thanks your detailed explanation.
and i just want to use one calendar to select date, your sql statement use two datetime(start date, end date).
how would i bind one calendar to two datetime? how to deal with the end date?
[quote=“win”]Hi Abishur, thanks your detailed explanation.
and i just want to use one calendar to select date, your sql statement use two datetime(start date, end date).
how would i bind one calendar to two datetime? how to deal with the end date?[/quote]
One way of doing this using nothing but an SQL query would be to use the ADDTIME function. Assuming you have a component called “Popup Calendar” there where clause would look something like this:
WHERE [Column for the tagname] = 'tagname' AND [column for the date] >= '{Root Container.Popup Calendar.date}' AND [column for the date] < ADDTIME('{Root Container.Popup Calendar.date}', '0 0:0:59.0')This would take the time you selected in the Calendar object and add 59 seconds to get the end time