I have a table in PostgreSQL database which has a column of type timestamp. Now I need to get data of all fields by specifying only date part of the timestamp column. How can I do this via SQL query?
Add a comment
|
1 Answer
Cast your column to date:
select ...
from ...
where the_timestamp_column::date = date '2017-01-28';
The cast using ::date is Postgres specific syntax. If you prefer ANSI SQL, use an explicit cast()
select ...
from ...
where cast(the_timestamp_column as date) = date '2017-01-28';
Note that the above condition won't make use of an index on the_timestamp_column. If you need that, either create an index on the expression or use a range query:
select ...
from ...
where the_timestamp_column >= timestamp '2017-01-28 00:00:00'
and the_timestamp_column < timestamp '2017-02-01 00:00:00';