CODEWITHSUNDEEP
mysqlsqldatetime
sehummel
sehummel

Reputation: 5568

Having trouble with WHERE DATEDIFF in my SQL

I think I'm going about this wrong. I want to pull the rows in my database that are less than a day old, in PHP. I'm using Laravel, but that's not relevant. Here is what I have:

if ($day == 'today') {
    $date = date('Y-m-d H:i:s');
} elseif ($day == 'yesterday') {
    $date = date('Y-m-d H:i:s', strtotime('yesterday'));
} else {
    ...
}

$sql = "SELECT * FROM all_forms_main_contact WHERE DATEDIFF(timestamp, '" . $date . "') <= 1";
$mc = DB::select($sql);
return json_encode($mc);

However, that isn't return the right results. It's return results older than a day.

Upvotes: 1

Views: 3690

Answers (5)

will
will

Reputation: 149

Whereas DATEDIFF ignores the time component (see DATEDIFF - MariaDB Knowledge ), TIMESTAMPDIFF doesn't. So this would work:

SELECT ... WHERE TIMESTAMPDIFF(DAY, NOW(), `timestamp`) = '0'

For example if NOW() = '2020-01-02 00:00:01' and timestamp is '2020-01-01 01:00:00' the above code would accurately return 0 days, but DATEDIFF would incorrectly return 1 day (as it ignores the time component.

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272036

This query selects rows that are newer than -24 hours.
For example if it is 04:00 AM then return all rows >= yesterday 04:00 AM:

SELECT * FROM `tbl` WHERE `datetime` >= NOW() - INTERVAL 1 DAY

Likewise for newer than -48 hours.
For example if it is 04:00 AM then return all rows >= day before yesterday 04:00 AM:

SELECT * FROM `tbl` WHERE `datetime` >= NOW() - INTERVAL 2 DAY

This query selects today's rows.
For example if it is 04:00 AM then return all rows >= today 00:00 AM:

SELECT * FROM `tbl` WHERE `datetime` >= CURDATE()

Likewise, this query selects yesterday's rows only.
For example if it is 04:00 AM then return all rows between yesterday 00:00 AM through (but not including) today 00:00 AM:

SELECT * FROM `tbl` WHERE `datetime` >= CURDATE() - INTERVAL 1 DAY AND `datetime` < CURDATE()

I use the above syntax for dates queries, which I believe is far more readable than date add/sub functions.

Upvotes: 1

spencer7593
spencer7593

Reputation: 108370

It really depends on how you define "less than a day old".

The MySQL DATEDIFF function only considers the date portion of the datetime values, and returns an integer number of days difference. Both of the queries below will return a value of 1, representing "1 day", where one is a difference of 8 hours, the other is a difference of 46 hours:

SELECT DATEDIFF('2013-01-15 07:00','2013-01-14 23:00') AS 8_hrs
SELECT DATEDIFF('2013-01-15 23:00','2013-01-14 01:00') AS 46_hrs

So, it's entirely possible and expected that rows "older" than 24 hours might be returned by your query.

(The hh:mi:ss time portion of the literal being supplied as an argument to the DATEDIFF function is being ignored, and the time portion of the timestamp values are being ignored. You would get the same result by supplying just the YYYY-MM-DD value.)

Also note that the predicate on the DATEDIFF function is not sargable, that is, MySQL cannot do a range scan on an index to satisfy that predicate.

For best performance, we generally prefer equivalent predicates that are on the bare column, e.g.

WHERE `timestamp` >= expr

which enables the optimizer to at least consider using a range scan on an index on the timestamp column.

If you are looking for row less than 1 day old (meaning less than 24 hours old, or less than 86400 seconds old), with a resolution up to a second, you could use a predicate like one of these:

WHERE `timestamp` >= '2013-01-15 16:10' + INTERVAL -1 DAY
WHERE `timestamp` >= '2013-01-15 16:10' + INTERVAL -24 HOUR
WHERE `timestamp` >= '2013-01-15 16:10' + INTERVAL -86400 SECOND

The DATE_ADD function is equivalent:

WHERE `timestamp` >= DATE_ADD('2013-01-15 16:10',INTERVAL -1 DAY)

Also note that it's not necessary to pass in the current date value as a literal in the SQL text; MySQL provides builtin functions that return the current date and time from the MySQL server, such as NOW(), CURRENT_DATE() and SYSDATE().

For rows that are less than 24 hours old or 48 hours old, respectively:

WHERE `timestamp` >= DATE_ADD(NOW(),INTERVAL -1 DAY)
WHERE `timestamp` >= DATE_ADD(NOW(),INTERVAL -2 DAY)

Upvotes: 4

Bohemian
Bohemian

Reputation: 424983

You would normally code it simply like this:

...
where timestamp > subdate(now(), 1)

Note the convenient version of subdate() that allows days to be specified as a simple integer (ie the default interval type is DAY). Also, subdate returns a datetime if the first parameter is a daretine.

Upvotes: 0

Kermit
Kermit

Reputation: 34055

Why not do:

SELECT ... WHERE timestamp BETWEEN DATE_SUB(NOW(), INTERVAL 1 DAY) AND NOW()

Upvotes: 0

Related Questions