Reputation: 436
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?
Upvotes: 0
Views: 7000
Reputation:
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';
Upvotes: 4