CODEWITHSUNDEEP
phpmysqlpdo
Thomas Aquinas
Thomas Aquinas

Reputation: 66

MySQL and PDO query discrepancies

I have a problem to which I have not found a solution, the same query made from PHP with PDO returns different results when executed directly from the MySQL console (the results from the console are as expected).

From PDO it returns 2 results and MySQL 3 results.

I will give some context:

Data:

SELECT id, startDate, endDate FROM  publications_elements;
+----+---------------------+---------------------+
| id | startDate           | endDate             |
+----+---------------------+---------------------+
|  1 | 2021-05-08 21:00:00 | NULL                |
|  2 | 2021-05-08 00:00:00 | 2021-05-08 20:00:00 |
|  3 | NULL                | 2021-05-08 23:20:00 |
|  4 | NULL                | NULL                |
+----+---------------------+---------------------+

Problematic query

SELECT
    id, 
    startDate,
    endDate
FROM publications_elements 
WHERE
    (UNIX_TIMESTAMP(startDate) <= 1620531853 OR startDate IS NULL) AND 
    (UNIX_TIMESTAMP(endDate) > 1620531853 OR endDate IS NULL)

Results in the MySQL console

+----+---------------------+---------------------+
| id | startDate           | endDate             |
+----+---------------------+---------------------+
|  1 | 2021-05-08 21:00:00 | NULL                |
|  3 | NULL                | 2021-05-08 23:20:00 |
|  4 | NULL                | NULL                |
+----+---------------------+---------------------+

Results with PHP PDO

+----+---------------------+---------------------+
| id | startDate           | endDate             |
+----+---------------------+---------------------+
|  1 | 2021-05-08 21:00:00 | NULL                |
|  4 | NULL                | NULL                |
+----+---------------------+---------------------+

Any idea?

Thanks in advance.

EDIT 1

Code in PHP

$timestamp = (new \DateTime)->getTimestamp();
$SQL = "SELECT id, startDate, endDate FROM publications_elements WHERE (UNIX_TIMESTAMP(startDate) <= {$timestamp} OR startDate IS NULL) AND (UNIX_TIMESTAMP(endDate) > {$timestamp} OR endDate IS NULL)";
$prepared = $PDOInstance->prepare($SQL);
$prepared->execute();
$result = $prepared->fetchAll(\PDO::FETCH_OBJ);

Upvotes: 1

Views: 33

Answers (1)

Thomas Aquinas
Thomas Aquinas

Reputation: 66

Ok, the comment about the timezone got me thinking (although it wasn't the problem, as I have synchronized since PDO initialization the PHP and MySQL zones).

I noticed that the startDate and endDate fields are stored as Y-m-d H:i:s, however the seconds are always 00 so in very close hours where seconds count there were discrepancies. I solved this by removing the seconds from the object.

Something like:

$timestamp = \DateTime::createFromFormat('Y-m-d H:i:s', date('Y-m-d H:i:00'))->getTimestamp();

Upvotes: 1

Related Questions