Mysql Question posted in
The official MySQL documentation can be found here.

PHP equivalent for MySQL's DATEDIFF

I have a problem with date comparing logic in MySQL and PHP: date difference functions return different results for the same dates. Example:

SQL

SELECT DATEDIFF('2024-06-02', '2024-05-03 12:57')
30

PHP

$x = date_diff( new DateTime( '2024-06-02' ), new DateTime( '2024-05-03 12:57' ) );

enter image description here

This is causing an assertion error when I pass data selected from the DB with the where DATEDIFF... criteria to PHP that checks the date with the date_diff() function.

Is there a strict equivalent for MySQL function in PHP (or vice versa), so that both return 29 (or 30)?

Tags:

2

Answers


  1. 0

    Both should return 30 but the PHP code you have written doesn’t account for time so it reports 29.

    If you normalize both to midnight, it will report 30 days.

    echo $x = date_diff( 
    (new DateTime( '2024-06-02' ))->setTime(0,0), 
    (new DateTime( '2024-05-03 12:57' ))->setTime(0,0) 
)->days;
// Outputs: 30
    Login or Signup to reply.
  2. 0

    You are essentially using two functions that have different purposes and mechanisms here.

    MySQL DATEDIFF()

    This function is ignoring the timestamps entirely. So even if the overlapping times would add or remove a day, it’s irrelevant because it’s simply stripping them off and then counting how many days are between the dates only. So MySQL will always return 30 regardless of the time in both parts.

    PHP date_diff()

    This function is checking how much total time exists between the two DateTime objects – the important bit here is that it’s taking the entire time into account, too, which means you can have a whole range from nearer 29 days to nearer 31 days in the answer depending on the time of day in both timestamps.

    Solutions

    The thing missing from the question is what you would like to happen. So here are both ways of approaching this. Either: a) tell MySQL to do the same as PHP, and compare the periods of time, or b) tell PHP to do the same as MySQL, and ignore the time.

    If you want the (rounded down) whole days between the two TIMESTAMPS:

    Instead of DATEDIFF() use DAY with TIMESTAMPDIFF() to get the number of whole days between the timestamps.

    SELECT
    TIMESTAMPDIFF(DAY, '2024-05-03 12:57', '2024-06-02') AS days;

    This is the MySQL equivalent of your PHP:

    echo date_diff(
    new DateTime("2024-06-02"),
    new DateTime("2024-05-03 12:57")
)->days;

    And both will return: 29

    If you want to ignore the times in both situations and just compare the two DATES only:

    Then your MySQL:

    SELECT
    DATEDIFF('2024-06-02', '2024-05-03 12:57') AS days;

    Would be equivalent to removing the time (well, setting it to 00:00) in PHP:

    echo date_diff(
    (new DateTime("2024-06-02"))->setTime(0, 0), 
    (new DateTime("2024-05-03 12:57"))->setTime(0, 0) 
)->days;

    Which would both output: 30

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search