Reputation: 698
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? :)
Upvotes: 3
Views: 12237
Reputation: 698
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'
Upvotes: 2
Reputation: 5599
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.
Upvotes: 2