SQL Query select items between two times incremented every 30 seconds

Question

I am trying to select tags and other properties from these tables between two dates and eventually select tags from a specified date range on one minute increments, as tags are uploaded every few seconds. Currently my last AND statement does not seem to be selecting my tags from the specified date range I want. I am looking into this and have seen similar posts but am stuck on why 1: I am not getting these from the date range below. 2: How to select tags between date range on 1 minute increments...

SELECT TOP (1000) FloatTable.[DateAndTime]
      ,FloatTable.[Millitm]
      ,FloatTable.[TagIndex]
      ,FloatTable.[Val]
      ,FloatTable.[Status]
      ,FloatTable.[Marker]
      ,TagTable.[TagName]
      ,TagTable.[TagType]
      ,TagTable.[TagDataType]
  FROM [FactoryTalk_Datalog].[dbo].[FloatTable] as FloatTable
  JOIN [FactoryTalk_Datalog].[dbo].[TagTable] as TagTable
  on FloatTable.[TagIndex] = TagTable.[TagIndex]
  WHERE TagTable.[TagName] = '[PLC]FI225'
  OR TagTable.[TagName] = '[PLC]FI250'
  OR TagTable.[TagName] = '[PLC]FI220'
  OR TagTable.[TagName] = '[PLC]AT_FI510'
  OR TagTable.[TagName] = '[PLC]AT_AI500'
  OR TagTable.[TagName] = '[PLC]SS_FIT1109'
  AND FloatTable.[DateAndTime] BETWEEN '2022-04-01 23:53:00.000' AND '2022-04-01 23:58:00.000'

I have tried specifying the date up front and then the tags which works but then pulls in all tags and I need select few tags.

I have looked at other stack posts that work for two dates but they are selecting all and not a set of certain items as listed above.

MySQL and SQL Server are two totally different RDBMSs, and you're clearly not using both of them simultaneously. Please [edit] your post to remove the one that does not actually apply to your question. If you're not sure which one to remove, stop trying to write SQL until you figure it out. Syntax and functionality differs widely between RDBMS systems. Tag spamming here is a very good way to get your question closed. Tags have relevance and meaning here, and should not be misused. — Ken White, Nov 09 '22 at 01:00

score 1 · Answer 1 · answered Nov 09 '22 at 01:04

1

you can either use parenthesis around the rags or use the IN clause The problems exist because the AND has precedence before OR see https://learn.microsoft.com/en-us/sql/t-sql/language-elements/operator-precedence-transact-sql?view=sql-server-ver16

SELECT TOP (1000) FloatTable.[DateAndTime]
      ,FloatTable.[Millitm]
      ,FloatTable.[TagIndex]
      ,FloatTable.[Val]
      ,FloatTable.[Status]
      ,FloatTable.[Marker]
      ,TagTable.[TagName]
      ,TagTable.[TagType]
      ,TagTable.[TagDataType]
  FROM [FactoryTalk_Datalog].[dbo].[FloatTable] as FloatTable
  JOIN [FactoryTalk_Datalog].[dbo].[TagTable] as TagTable
  on FloatTable.[TagIndex] = TagTable.[TagIndex]
  WHERE (TagTable.[TagName] = '[PLC]FI225'
  OR TagTable.[TagName] = '[PLC]FI250'
  OR TagTable.[TagName] = '[PLC]FI220'
  OR TagTable.[TagName] = '[PLC]AT_FI510'
  OR TagTable.[TagName] = '[PLC]AT_AI500'
  OR TagTable.[TagName] = '[PLC]SS_FIT1109')
  AND FloatTable.[DateAndTime] BETWEEN '2022-04-01 23:53:00.000' AND '2022-04-01 23:58:00.000'

OR

SELECT TOP (1000) FloatTable.[DateAndTime]
      ,FloatTable.[Millitm]
      ,FloatTable.[TagIndex]
      ,FloatTable.[Val]
      ,FloatTable.[Status]
      ,FloatTable.[Marker]
      ,TagTable.[TagName]
      ,TagTable.[TagType]
      ,TagTable.[TagDataType]
  FROM [FactoryTalk_Datalog].[dbo].[FloatTable] as FloatTable
  JOIN [FactoryTalk_Datalog].[dbo].[TagTable] as TagTable
  on FloatTable.[TagIndex] = TagTable.[TagIndex]
  WHERE TagTable.[TagName] IN( '[PLC]FI225','[PLC]FI250',
'[PLC]FI220', '[PLC]AT_FI510', '[PLC]AT_AI500', '[PLC]SS_FIT1109')
  AND FloatTable.[DateAndTime] BETWEEN '2022-04-01 23:53:00.000' AND '2022-04-01 23:58:00.000'

answered Nov 09 '22 at 01:04

nbk

45,398
8
30
47

Thanks, any suggestions on selecting these for 30 second intervals between those times? – lilrobby Nov 09 '22 at 01:11
sure group by seconds https://stackoverflow.com/questions/9814930/group-datetime-into-5-15-30-and-60-minute-intervals – nbk Nov 09 '22 at 01:16
For some reason the query does not work and returns blank. I believe it has to do with the date time format. The entries in the column are setup as '2022-04-01 23:53:00.000' and have verified the column is a date time object. I will try converting column to string and see. Thanks for your help! – lilrobby Nov 09 '22 at 01:44
Doing this worked for me, Not sure why but it will not work when specifying the hours, minutes, seconds... AND FloatTable.[DateAndTime] >= '2022-04-01' AND FloatTable.[DateAndTime] < '2022-04-02' – lilrobby Nov 09 '22 at 01:53
you need only to remove the milliseconds. databses convert date string automatically they are very picky about it – nbk Nov 09 '22 at 08:30
Update on what I have: Now I am having issues trying to select from my second column only values with 30 or 0. I need a subquery or something. I have tried in and statement but can not do comparisons with a temp column,... – lilrobby Nov 11 '22 at 21:20
Accept this answer and ask a new one, comments are no place to post code – nbk Nov 11 '22 at 23:56

SQL Query select items between two times incremented every 30 seconds

1 Answers1