CODEWITHSUNDEEP
postgresql
user2365917
user2365917

Reputation: 1185

postgres query with current_date is not working

Below is my query in postgres

select dns_time,update_time from dns_lookup where update_time=current_date;

Here update_time is of type timestamp with time zone. I have tried this one also.

select dns_time,update_time from dns_lookup where update_time like current_date;

Then also getting no results. I have records with current_date values.But it is not showing me any records.How to compare timestamp with time zone value with current_date.please help me Thank you.

Upvotes: 1

Views: 3824

Answers (2)

Gopinagh.R
Gopinagh.R

Reputation: 4916

You might be trying to compare a timestamp field with the Date field.

Try casting timestamp to Date

select dns_time,update_time from dns_lookup where update_time::Date=current_date;

Hope this helps.

Upvotes: 2

user330315
user330315

Reputation:

First: LIKE on a date or timestamp column does not make any sense.

A timestamp contains a time part so a comparison with a plain date won't work. You need to remove the time from the timestamp by casting it to a date:

where cast(update_time as date) = current_date;

Note that this will void the usage of an index on update_time. If that is a problem you can create a function based index on that expression.

Upvotes: 6

Related Questions