get 2 days ago query in mysql

Question

So for getting last 24 hours query I use something like this

SELECT COUNT(*) AS cnt FROM `mytable` WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 1 DAY)

where timestamp is a table field with timestamps.

but how can I get the interval between 2 days ago and yesterday. So

today is 24 ian. I want a query between 22 ian (00:00am) and 23 ian (00:00am)

Answer 1

WHERE timestamp BETWEEN 
        DATE_SUB(DATE(NOW()), INTERVAL 2 DAY)
         AND DATE_SUB(DATE(NOW()), INTERVAL 1 DAY) 

Answer 2

If you want a query between 22 Jan (00:00 AM) and 22 Jan (11:59 PM)

where DATE(timestamp) = DATE_SUB(DATE(now()), INTERVAL  2 day);

Example: timestamp = 2020-02-24 12:07:19 and Date(timestamp) is 2020-02-24 and now() output is current date with time when we use DATE(now()) then output is Date only,

DATE_SUB(DATE(now()), INTERVAL  2 day)

is will be 2 days ago.