postgres query with current_date is not working

Question

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.

user330315 · Accepted Answer · 2013-06-07 07:36:07Z

6

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.

answered Jun 7, 2013 at 7:36

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Gaurav Over a year ago

works flawlessly!

Gopinagh.R · Accepted Answer · 2013-06-07 07:53:08Z

2

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.

answered Jun 7, 2013 at 7:53

Gopinagh.R

4,9514 gold badges47 silver badges60 bronze badges

Collectives™ on Stack Overflow

postgres query with current_date is not working

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related