CODEWITHSUNDEEP
phpmysqlsqldatediff
Philip-Marcel Kost
Philip-Marcel Kost

Reputation: 11

MySQL Datediff - what is the issue?

I have the following problem. My database looks like this:

Database structure

I want to fetch all data from this database which is older than 2 days and the status = "Betreiber informiert" or status = "Betreiber informiert (manuell)"

I have created a query but it is not working. Can someone help me?

This is my query:

SELECT * 
FROM meldungen 
WHERE status = 'Betreiber informiert' 
   OR status = 'Betreiber informiert(manuell)' 
  AND DATEDIFF(NOW(), Meldungszeitpunkt) > 172800

If I use this query, it looks like this:

Result with my query

You can see my query selects all rows which have status='Betreiber informiert' or 'Betreiber informiert(manuell)' but the days are not considered.

Can someone help me?

Upvotes: 0

Views: 76

Answers (3)

Sentry
Sentry

Reputation: 81

I think you just have to change your code to the following:

SELECT * 
FROM meldungen 
WHERE status = 'Betreiber informiert' 
  AND DATEDIFF(NOW(), Meldungszeitpunkt) > 172800 
   OR status = 'Betreiber informiert(manuell)' 
  AND DATEDIFF(NOW(), Meldungszeitpunkt) > 172800

You have to write the AND Task for every OR

With your old code it is just searching for "Betreiber informiert" from every timestamp and for "Betreiber informiert (manuell)" that is older than two days.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269533

Huh? What is 172800?

Try this:

SELECT m.*
FROM meldungen m
WHERE m.status IN ('Betreiber informiert', 'Betreiber informiert(manuell)') AND
      m.Meldungszeitpunkt < NOW() - interval 2 day;

Note that this also simplifies the logic to use IN -- because I am guessing that you want the date condition applied to both statuses. If you really only want the date condition applied to the second one, then you would use:

WHERE m.status = 'Betreiber informiert' OR
      (m.status = 'Betreiber informiert(manuell)') AND
       m.Meldungszeitpunkt < NOW() - interval 2 day
      )

That interpretation seems much less likely to me (although it is what your query does).

You might want curdate() rather than now(), unless you want to take the time into account.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520948

172800 is the numbers of seconds in two full days. If you really want to do it this way, then you should be using TIMESTAMPDIFF:

SELECT m.*
FROM meldungen m
WHERE
    m.status IN ('Betreiber informiert', 'Betreiber informiert(manuell)') AND
    TIMESTAMPDIFF(SECOND, Meldungszeitpunkt, NOW()) > 172800;

Note that I use WHERE IN to avoid the problem of order of operation between your ORs and ANDs. AND has highest precedence than OR, so your original WHERE clause was actually evaluating as this:

WHERE
    status = 'Betreiber informiert' OR
    (status = 'Betreiber informiert(manuell)' AND
     DATEDIFF(NOW(), Meldungszeitpunkt) > 172800)

Upvotes: 2

Related Questions