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

Isn't potatoeDate is of type timestamp? That would explain the behaviour — Adam
– Adam, Commented Oct 30, 2017 at 21:58

Adam · Accepted Answer · 2017-10-30 22:02:20Z

2

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.

answered Oct 30, 2017 at 22:02

Adam

5,6296 gold badges35 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

newOne Over a year ago

Indeed, you are right! The only solution is add whole timestamp to query with interesting range of hours? You know how to solve it in other way/s? I mean, for example: where potatoeDate between '2017/05/07'::timestamp and '2017/05/08 23:59:59'

Adam Over a year ago

If you are just interested in limiting range in days then I suggest adding 1 day to the upper limit (just as you did in the last example: potatoeDate < ('2017/05/08'::date)+1;), or, more elegant + INTERVAL '1 day'.

newOne · Accepted Answer · 2017-10-30 22:31:37Z

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'

answered Oct 30, 2017 at 22:31

newOne

7063 gold badges9 silver badges28 bronze badges

2 Comments

Adam Over a year ago

Well, it's easy to write, but it's more expensive to evaluate by database. I wouldn't do that...

newOne Over a year ago

It is more expensive, because DB must cast all records to date format, ook understood, again You are right. So, the simple concatenation is more effective in this situation.

Collectives™ on Stack Overflow

Postgres: sql query by filter date range

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related