Postgres query timestamp by datepart only

Question

I'm using this to try and get all events with a timestamp of today

SELECT systemuserid,ondate from auditlog 
WHERE ondate::date = NOW()::date

Performance seems really bad, over 2 minutes. I have a index on ondate.

Is there a more efficient way to do it?

Thanks

score 2 · Accepted Answer · 2020-06-23 12:32:39Z

2

Use a range condition:

select *
from auditlog
where ondate >= current_date
  and ondate < current_date + 1;

current_date will be converted to a timestamp at midnight, so ondate => current_date will include everything from "today".

And the condition ondate < current_date + 1 will exclude rows with a timestamp of tomorrow (midnight) or later.

answered Jun 23, 2020 at 12:30

user330315

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

1 Comment

Thanks, that does it in under a second!