-1

To demonstrate the issue I am having, I am using a single table which has a DATE and a TIME field as part of the record. For the example I wish to filter from 07:00 on one day to 06:59 the next day, so my query is as follows:

SELECT * FROM V_W1W2_LOGS WHERE (V_W1W2_LOGS.W1_DATE >= '2015-05-20' AND V_W1W2_LOGS.W1_TIME >= '07:00:00') AND (V_W1W2_LOGS.W1_DATE<='2015-05-22' AND V_W1W2_LOGS.W1_TIME < '07:00:00')

(Above will not return any rows. Seems to behave as if there was no change of precedence from the brackets)

As a test I did the following. If I remove the time constraint from the 2nd part of the 'AND' the rows will be returned, starting at the 07:00 but for the full period of the following day.

(...) WHERE (V_W1W2_LOGS.W1_DATE >= '2015-05-20' AND V_W1W2_LOGS.W1_TIME >= '07:00:00') AND (V_W1W2_LOGS.W1_DATE<='2015-05-22')

(Returns Rows from 07:00 as expected)

Is there something obviously wrong with the query? I tried more brackets, different date formats etc but it made no difference. It seems to behave as if the brackets were not there.

I have not seen any issues to do with this in my trawl. If there was a timestamp in the record it would probably more straightforward but this is not the case. Any help appreciated.

Thanks

James F

fantaghirocco
  • 4,345
  • 6
  • 34
  • 46
James Fergus
  • 21
  • 4

2 Answers2

1

The first query is using both:

V_W1W2_LOGS.W1_TIME >= '07:00:00'

... AND ...

V_W1W2_LOGS.W1_TIME < '07:00:00'

... which is a nonsense.

Maybe did you mean OR in between the two?

fantaghirocco
  • 4,345
  • 6
  • 34
  • 46
  • The Time expression is used twice but is within parenthesis AND 'ed with a different date. I would have expected the 2 expressions within the brackets to be evaluated first? – James Fergus May 25 '15 at 12:47
  • `AND` conditions are always evaluate together. You must think about parenthesis only if you write `(... AND ...) OR (... AND ...)` – fantaghirocco May 25 '15 at 12:51
  • write `... AND ... AND ... AND ...` is the same as `(... AND ...) AND (... AND ...)` – fantaghirocco May 25 '15 at 12:53
0

In addition to the answer to fanaghirocco.

You should use timestamp instead date and time separately. Something like that:

SELECT * FROM table1
 WHERE
cast (fdate ||' '||ftime as timestamp) >= '2015-05-01 07:00:00'
 and
cast (fdate ||' '||ftime as timestamp) < '2015-05-02 07:00:00'
Val Marinov
  • 2,495
  • 13
  • 19