php DateTime diff consider only Y-m-d format

Question

In mysql db I've a timestamp field (dateexp) defined in my t1 table. with a query I consider only format Y-m-d of timestamp to find records. Use curdate() instead of now()

SELECT * FROM t1 WHERE dateexp < currdate()

I would like to do the same thing with php using DateTime class for find a number of days until a date but I can't understand how I could do it comparing only the Y-m-d format.

I tried in this way but I use Y-m-d H:i:s format.

$row['dateexp'] = "2013-11-10 12:00:00";

$d1 = new DateTime();
$d2 = new DateTime($row['dateexp']);
$interval = $d1->diff($d2);
$days = $interval->format('%r%a');

How could I do this for comparing only Y-m-d? I tried with DateTime::createFromFormat but probably in wrong way..

Thanks

Please, clarify what do you mean 'only dates'. So you need to create `DateTime` object only with date part? Or find a difference in days (so time part matters)? — Alma Do, Nov 06 '13 at 10:08
I'd like to find diff of 2 dates with format Y-m-d (2013-11-05) and not with format Y-m-d H:i:s (2103-11-05 11:00:00). hours, min and sec affect the result depending on whether the time is before or after — Paolo Rossi, Nov 06 '13 at 10:42

score 2 · Accepted Answer · answered Nov 06 '13 at 10:51

You have several possible ways to do this. First, you can select date string in desired format from DBMS:

SELECT *, DATE(dateexp) AS date_holder FROM t1 WHERE dateexp < currdate()

and then apply it in your DateTime object:

$d1 = new DateTime(date('Y-m-d'));
$d2 = new DateTime($row['date_holder']);
$interval = $d1->diff($d2);
$days = $interval->format('%r%a');

Second - you can, as you've mentioned, create DateTime from format:

$d1 = new DateTime(date('Y-m-d'));
$d2 = new DateTime(date('Y-m-d', strtotime($row['dateexp'])));
$interval = $d1->diff($d2);
$days = $interval->format('%r%a');

-so you'll be able to avoid selection of additional field in DBMS

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

PHP

Don't mix date() and strtotime() functions with DateTime extension.

You can just create DateTime object with keyword today to set today date without time, or use DateTime::setTime() on DateTime object, to set time you wish:

$d1 = new DateTime('today');
$d2 = new DateTime($row['dateexp']);
$d2->setTime(0, 0);
echo $d1->diff($d2)->format('%r%a');

Demo.

MySQL

Or you can already calculate days difference in the MySQL, with function TIMESTAMPDIFF:

SELECT TIMESTAMPDIFF(DAY, CURDATE(), DATE('2013-11-10 12:00:00'))

or in your case:

SELECT *, TIMESTAMPDIFF(DAY, CURDATE(), DATE(dateexp)) AS diff_in_days
FROM t1 
WHERE dateexp < CURDATE()

php DateTime diff consider only Y-m-d format

2 Answers2

PHP

MySQL