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?
1 Answer
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';
7 Comments
Palace Chan
wish i had read the second part before submitting the first style query to a large table...
moveson
You can compare timestamps directly with date strings. Postgres is smart enough to do the casting for you.
where the_timestamp_column >= '2015-07-15' and the_timestamp_column < '2015-07-16'
Rajkumar M
select * from the_table where the_timestamp_column::date = date '2015-07-15'; worked for me. Thanks.
Gaurav
the casting in date '2015-07-15' is not required!
ibrahimyanik
you can use DATE(the_timestamp_column) select * from the_table where DATE(the_timestamp_column) = date '2015-07-15';
|