What is the most efficient way to extract a date from a timestamp in PostgreSQL?
To summarize the comments, your first and third solution are identical. Casting to a date simply uses the date
function according to @Nick Barnes.
Those options, plus option 2, requires a function to be run against every row of the table, so even if you have an index, it cannot be used.
Assuming there is an index on created_on
, this is your best bet:
select * from foowhere created_on >= '2014/1/1 00:00:00' and created_on < '2014/1/2 00:00:00';