How to compare dates in datetime fields in Postgresql?

Question

I came across a strange scenario when comparing dates in postgresql (version 9.2.4 in windows). I have a column in my table saying update_date with type timestamp without a time zone. The client can search this field only with a date (for example, 2013-05-03) or with a date with time (i.e. 2013-05-03 12:20:00). This column has the value as a timestamp for all rows at the moment and has the same date (2013-05-03), but the time difference.

When I compare this column, I get different results. Like the following:

select * from table where update_date >= '2013-05-03' AND update_date <= '2013-05-03' -> No results select * from table where update_date >= '2013-05-03' AND update_date < '2013-05-03' -> No results select * from table where update_date >= '2013-05-03' AND update_date <= '2013-05-04' -> results found select * from table where update_date >= '2013-05-03' -> results found 

My question is: how can I make the first query possible to get results, I mean, why does the third query work, but not the first?

Can someone help me? Thanks in advance.

Answer 1

@Nicolai is right about casting and why the condition is false for any data. I assume you prefer the first form because you want to avoid manipulating dates on the input line, right? you need not be afraid:

 SELECT * FROM table WHERE update_date >= '2013-05-03'::date AND update_date < ('2013-05-03'::date + '1 day'::interval); 

Answer 2

When you compare update_date >= '2013-05-03' postgres values, the values ​​for the same type are compared. Thus, your "2013-05-03" has been translated to "2013-05-03 00:00:00".

So, for update_date = '2013-05-03 14:45:00' your expression will be like this:

 '2013-05-03 14:45:00' >= '2013-05-03 00:00:00' AND '2013-05-03 14:45:00' <= '2013-05-03 00:00:00' 

It is always false

To solve this problem, add update_date to date :

 select * from table where update_date::date >= '2013-05-03' AND update_date::date <= '2013-05-03' -> Will return result 

Answer 3

Use the range type. If the user enters a date:

 select * from table where update_date <@ tsrange('2013-05-03', '2013-05-03'::date + 1, '[)'); 

If the user enters timestamps, you do not need ::date + 1 part

http://www.postgresql.org/docs/9.2/static/rangetypes.html

http://www.postgresql.org/docs/9.2/static/functions-range.html