Filter results range time query

Ignition
,
1

Hello, how can I filter results with a query for every day with a specific time , for example:

Schedule                               |  Results
08:00:00 am  -  13:00:00 pm            | cod_prod = 2142567 , dateDatabase = '2022-08-06 09:39:40'
                                         cod_prod = 2142567 , dateDatabase = '2022-08-06 11:58:10'
                                         cod_prod = 0978672 , dateDatabase = '2022-08-06 12:04:23'

I have this code:

select cod_prod, dateDatabase from table1 where time_format(dateDatabase,'%r') between '01:00:00' and '02:52:55' ;

Because I am going to put a the query in a graphic in ignition and then show the information for a specific schedule

2

Only the time matters, and not the date?

Also, which SQL server are you using (ie mysql, mssql, etc)

and it would also be helpful to know the datatype of the column you are pulling the date information from.

4
  • I am using MySQL 8.0.
  • The both matters Date and month but I think I need to do an operation like current_day -1 to star since a day before.
  • The datatype is DateTime.
1 Like
5

let me check, thanks

6

If I understand what you are wanting to do correctly, this is the query you are looking for:

SELECT `cod_prod`, `dateDatabase` FROM YOURDATABASENAME.table1 
WHERE `dateDatabase` >= NOW() - INTERVAL 1 DAY AND Time(`dateDatabase`) BETWEEN '01:00:00' AND '02:52:55'