CODEWITHSUNDEEP
mysqlsql
OWADVL
OWADVL

Reputation: 11154

get 2 days ago query in mysql

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)

Upvotes: 17

Views: 32682

Answers (5)

susheel sahoo
susheel sahoo

Reputation: 107

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.

Upvotes: 3

bonCodigo
bonCodigo

Reputation: 14361

You can also try DATE_ADD with a minus interval ;)

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

Upvotes: 5

Sashi Kant
Sashi Kant

Reputation: 13465

Try BETWEEN::

SELECT 
COUNT(*) AS cnt 

FROM `mytable` 

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

Upvotes: 1

Praveen Kumar Purushothaman
Praveen Kumar Purushothaman

Reputation: 167250

Use Interval

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

Upvotes: 4

Hamlet Hakobyan
Hamlet Hakobyan

Reputation: 33391

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

Upvotes: 34

Related Questions