ERROR: date/time field value out of range: "2020-06-0700:00:00"
You are overcomplicating things. concat() and to_char() are not needed and the root cause of the problem.

The start of the day 7 days back can be obtained using: current_date - 7. As that doesn't have a time part, a comparison with a timestamp column will automatically assume 00:00:00. 

If you want to compare everything up to yesterday midnight, simply use &lt; current_date. 

So your expression can be simplified to: 

and created_date &gt;= current_date - 7
and created_date &lt; current_date


Note the &lt; instead of &lt;= in the second condition.

If you want to be explicit about the time, you could use 

and created_date &gt;= (current_date - 7) + time '00:00:00'
and created_date &lt; current_date + time '00:00:00' 


But that's not needed and is absolutely identical to the first condition.



created_date &lt; current_date is roughly equivalent to created_date &lt;= (current_date - 1) + time '23:59:59' (which is your initial intention). 

However, it's only roughly equivalent because your attempt isn't actually correct as it misses the time between 23:59:59.000000 and 23:59:59.999999, that's why it's better to use &lt; with the next day's start.