Postgres: sql query by filter date range

Question

I have table Test which including date column potatoeDate. I want to take records by querying by date from this column. And when I querying by date from Y.AA.AAAA to X.AA.AAAA, always got records from Y.AA.AAAA to (X-1).AA.AAA. For example, i searching from 01.10.2017 to 30.10.2017, but I got records from range 01-29.10.2017.

I try everything what I know, event subqueries but nothing helped. My attempts:

    Select n1.potatoeDate 
    FROM (SELECT potatoeDate from test WHERE  potatoeDate > '2017/05/07'::date) n1
    WHERE  n1.potatoeDate <= '2017/05/08'::date;

    select * 
    from test
    where potatoeDate between '2017/05/07' and '2017/05/08'

    SELECT potatoeDate from test
    where
    potatoeDate >= '2017/05/07' AND
    potatoeDate <= '2017/05/08'

--little hack
    SELECT potatoeDate from test
    WHERE  
    potatoeDate >= '2017/05/07'::date
    AND 
    potatoeDate <= ('2017/05/08'::date)+1;

Only the last little hack query working. :|

Can someone help me? :)

Answer 1

I guess that potatoeDate is of type timestamp.

When you compare date with timestamp the timestamp is larger (is later in time) if it has even just one second. Try to cast a date to timestamp and see that it has 00:00:00 in time field. So timestamp with time different than 00:00:00 would be larger.

Answer 2

I found solution(information from @Adam helped). :)

In this situation its necessary to cast column name in where clause to date type. When its changed, all queries return expected results.

Example :

select * 
from test
where potatoeDate::date between '2017/05/07' and '2017/05/08'

SELECT potatoeDate from test
where
potatoeDate::date >= '2017/05/07' AND
potatoeDate::date <= '2017/05/08'