How to write a SQL statement which filters between two dates/times, with the date and time in separate columns

Question

I have an Informix database (over which I have no control other than executing SELECT statements) which contains the following columns:

Date | Time | Column1 | Column2 | Column3

Date is a date/time format, Time is an integer (storing, for example 8am as 800 and 1pm as 1300). The other columns are irrelevant for this purpose but let's say they are integers which I want to SUM.

What's the most efficient way I can SUM my columns Column1, Column2, and Column3? Let's say I want rows between 10am on August 1st 2019, and 11am on August 5th 2019.

My first attempt was:

SELECT Date, Time, SUM(Column1), SUM(Column2), SUM(Column3)
  FROM table
 WHERE (Date >= '08/01/19' AND Time >= 1000) AND (Date <= '08/05/19' AND Time < 1100);

But just thinking through the logic of that means I'm simultaneously asking for something to be >=1000 and < 1100. The result was only records between 10am and 11am on those days were returned.

My next attempt was:

SELECT Date, Time, SUM(Column1), SUM(Column2), SUM(Column3)
  FROM table
 WHERE ((Date = '08/01/19' AND Time >= 1000)
    OR (Date BETWEEN '08/02/19' AND '08/04/19')
    OR (Date = '08/05/19' AND Time < 1100));

This provides the result I'm looking for but doesn't seem very efficient - and potentially complex to construct programmatically.

If this is the best solution, I'm ok with that. Just hoping for someone smarter than me to put me right.

Answer 1

If you are storing the values in different columns, then you seem to want:

where (date > '2019-08-01' or
       date = '2019-08-01' and time > 1000
      ) and
      (date < '2019-08-05' or
       date = '2019-08-05' and time < 1100
      )

This would be simpler if the date/time values were stored in a single column.