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? :)
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.