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
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'