CODEWITHSUNDEEP
sqlpostgresql
Arjun A J
Arjun A J

Reputation: 436

PostgreSQL: how to specify WHERE condition with only date value for a column of type time stamp?

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

Answers (1)

user330315
user330315

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

Related Questions