How do I retrieve results from mysql where a timestamp is between 11pm yesterday and 11pm today?

I have a simple mysql table called messages with the following fields:

• ID(int)
• MESSAGE(varchar)
• CREATE_DATE(timestamp)

I simply want to make a query that returns all records where CREATE_DATE is between 11pm yesterday and 11pm today.

When saying yesterday and today I mean by getting the current date from my开发者_开发百科sql, not hardcoding it in.

---

select * from table where create_date 
between concat_ws(' ',curdate(),'23:00:00') - interval 1 day 
and
concat_ws(' ',curdate(),'23:00:00')