user2866264 user2866264 - 22 days ago 15
SQL Question

How to compare dates in datetime fields in Postgresql?

I have been facing a strange scenario when comparing between dates in postgresql(version 9.2.4 in windows). I have a column in my table say update_date with type 'timestamp without timezone'. Client can search over this field with only date (i.e: 2013-05-03) or date with time (i.e: 2013-05-03 12:20:00). This column has the value as timestamp for all rows currently and have the same date part(2013-05-03) but difference in time part.

When I'm comparing over this column, I'm getting different results. Like the followings:

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 the 3rd query is working but not the first one?

Can anybody help me with this? Thanks in advance.

Answer

@Nicolai is correct about casting and why the condition is false for any data. i guess you prefer the first form because you want to avoid date manipulation on the input string, correct? you don't need to be afraid:

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