SQL get a last updated DATETIME field from EXACTLY 3 weeks ago

Question

This is the same query I used in the past for a DATE field, however for a DATETIME field it doesn't work.

SELECT
    *
FROM 
    user_log
WHERE 
    log_updated = DATE_SUB(DATE(NOW()), INTERVAL 21 DAY);

I want a date from EXACTLY 21 days ago, but it can be of any time during that day. I do not want 21 days and over (or 21 days and under).

What is the DBMS you are using? Please don't tag multiple DBMS. — Ilyes, Oct 30 '18 at 20:06
No worries Hana, the generic sql tag will be removed later to prevent this. — S3S, Oct 30 '18 at 20:22

Madhur Bhaiya · Accepted Answer · 2018-10-30T20:16:07.323

4

You can do the following:

SELECT
    *
FROM 
    user_log
WHERE 
    DATE(log_updated) = DATE(NOW() - INTERVAL 21 DAY);

But then this is not an efficient (sargable) solution, as using a function on the Column will impede the usage of indexing (if defined).

So, a better approach would be:

SELECT
    *
FROM 
    user_log
WHERE 
    log_updated >= DATE(NOW() - INTERVAL 21 DAY) AND 
    log_updated < DATE(NOW() - INTERVAL 20 DAY)

Notice that I have used a day after the required date for maximum bound checking (not inclusive). It is obtained by INTERVAL -20 DAY

edited Oct 30 '18 at 20:16

answered Oct 30 '18 at 20:08

Madhur Bhaiya

2

@Hana Please set this answer as accepted answer if it solved your problem. thanks – Mohammad Ali Oct 30 '18 at 20:13

score 0 · Answer 2 · answered Oct 30 '18 at 22:45

Since DATE(NOW()) is the same as CURDATE(), you may as well use

WHERE log_updated >= CURDATE() - INTERVAL 21 DAY
  AND log_updated  < CURDATE() - INTERVAL 20 DAY

This may use an index on log_updated.

In contrast, DATE(log_updated) ... cannot use an index on log_updated.

2 Answers2