I want to fetch data between two dates but not getting the result with last date including I am writ-CodePudding

select * , s.schema_name, s.version 
from credential c, 
     schema s 
where c.schema_id = s.schema_id 
  and c.updated_at BETWEEN '2022-05-01' AND '2022-05-02' 
  and c.status = 'PROPOSAL_RECEIVED' 
  and c.deleted = false.

In my DB i have data for both the dates but not getting data for 02-05-2022. Please help thanks...

CodePudding user response：

The first date is inclusive, but the second one is exclusive without a timestamp because it will consider 00:00:00:000 as the time, so you could use the next day for it or also add the time

BETWEEN '2022-05-01' AND '2022-05-03'

You can also check this article for more info.

CodePudding user response：

If column "updated_at" were of type "date", then your query would work as written.

But if it is of type timestamp (with or without tz) then the endpoint is inclusive but it includes only the first millisecond of the specified day, not the entirety of that day.

If you only want dates, then use the "date" type.