[postgresql] Postgres where clause compare timestamp

I have a table where column is of datatype timestamp

Which contains records multiple records for a day I want to select all rows corresponding to day

How do I do it?

This question is related to postgresql

The answer is


Assuming you actually mean timestamp because there is no datetime in Postgres

Cast the timestamp column to a date, that will remove the time part:

select *
from the_table
where the_timestamp_column::date = date '2015-07-15';

This will return all rows from July, 15th.

Note that the above will not use an index on the_timestamp_column. If performance is critical, you need to either create an index on that expression or use a range condition:

select *
from the_table
where the_timestamp_column >= timestamp '2015-07-15 00:00:00'
  and the_timestamp_column < timestamp '2015-07-16 00:00:00';