How to compare dates in datetime fields in Postgresql?
@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 tableWHERE update_date >= '2013-05-03'::dateAND update_date < ('2013-05-03'::date + '1 day'::interval);
When you compare update_date >= '2013-05-03'
postgres casts values to the same type to compare values. So your '2013-05-03' was casted to '2013-05-03 00:00:00'.
So for update_date = '2013-05-03 14:45:00' your expression will be that:
'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'
This is always false
To solve this problem cast update_date to date
:
select * from table where update_date::date >= '2013-05-03' AND update_date::date <= '2013-05-03' -> Will return result
Use the range
type. If the user enter a date:
select *from tablewhere update_date <@ tsrange('2013-05-03', '2013-05-03'::date + 1, '[)');
If the user enters timestamps then you don't need the ::date + 1
part
http://www.postgresql.org/docs/9.2/static/rangetypes.html
http://www.postgresql.org/docs/9.2/static/functions-range.html