CODEWITHSUNDEEP
phpmysql
Torben
Torben

Reputation: 5494

PHP/MySQL Date and Time comparison (without seconds)

I created a DateTime Object with PHP and inserted it into the database column scheduled time - as this: 2017-03-13 07:30:00

Now I want to run a query and select the rows which have the same time or the time is less than the current time - assuming the current time is 2017-03-13 08:30:00. I want to ignore the seconds because those doesn't matter.

I do this:

SELECT * FROM fp_mail_reminder
        WHERE DATE_FORMAT(scheduledtime, '%d.%m.%Y %k:%i') <= DATE_FORMAT('2017-03-13 08:30:00', '%d.%m.%Y %k:%i')
        AND active = 1
        ORDER BY id ASC

However the query does not result a row which has been inserted with 2017-03-13 07:30:00. If I change the date as this: 2017-03-12 07:30:00 - so I change the day, the result is coming up.

Can anybody say why this is the case?

Upvotes: 1

Views: 1607

Answers (2)

Psi
Psi

Reputation: 6783

What you are doing is comparing the string values instead of the actual date values. The problem here is that the string comparison of '21.01.2017' is greater than '01.03.2017' although the date is older. This is, because the string comparison compares character by character, starting with the first one:

  • The first character of 21.01.2017 => 2
  • The first character of 01.03.2017 => 0

And 0 is smaller than 2 (even in Unicode, Latin1, ASCII, whatever your charset and collation is).

This is by the way the reason, that the ISO date format is YYYY-MM-DD HH:mm:ss. With this format, you comare the most significant parts first and compare by descending importance even when using the string representation.

If you really want to eliminate the seconds, I suggest to reduce the value by that amount:

 WHERE scheduledtime - interval second(scheduledtime) second <= cast('2017-03-13 08:30:00' as datetime)

Upvotes: 1

Sumon Sarker
Sumon Sarker

Reputation: 2795

Try this

SELECT * FROM fp_mail_reminder
WHERE DATE_FORMAT(scheduledtime, '%Y-%m-%d %k:%i') <= DATE_FORMAT('2017-03-13 08:30:00', '%Y-%m-%d %k:%i')
AND active = 1
ORDER BY id ASC

Upvotes: 0

Related Questions