mysql date is different when print from php

Question

I have field with type (timestamp) and default CURRENT_TIMESTAMP, the date is correct when I browsing records with phpMyAdmin but when I tried to display it by PHP file it displayed incorrect date

date in MySQL 2017-07-05 10:28:12

date in PHP 2017-06-23 09:51:31

using below query

SELECT u.date FROM table u

the global time zone of MySQL server is (SYSTEM) and I didn't change

PHP code

<?php
include("../config.php");
$sql = mysql_query("sql query");
var_dump(mysql_fetch_array($sql));
?>

Any support?

SOLUTION

If you face this problem you have to do the following:

Instead of mysql use PDO or mysqli_* functions and this will solve the problem without setting timezones either in your MySQL server or PHP files.

I suspect PhpMyAdmin is doing the conversion, because PHP doesn't do that by itself. — Barmar
– Barmar, Commented Jul 5, 2017 at 17:52

Paul T. · Accepted Answer · 2017-07-05 21:32:25Z

1

There must be some other issue (more to the code 'picture', perhaps? ... or some other config/setting?), as I cannot reproduce.

Here's my sample data:

Here's the structure for the date column:

The PHP code that I used:

$dsn = 'mysql:host=localhost;dbname=test';

try {
    $conn = new PDO($dsn, 'root', 'xxxxx');
} catch (PDOException $e) {
    echo 'Error: ' . $e->getMessage();
    die();
}

$stmt = $conn->prepare("SELECT t.date FROM tryme t");
$stmt->execute();
var_dump($stmt->fetchAll());

...and the output: (note that I used PDO handling in the above PHP code, the output would be similar if mysqli was used)

array(5) {
  [0]=>
  array(2) {
    ["date"]=>
    string(19) "2017-07-05 14:45:51"
    [0]=>
    string(19) "2017-07-05 14:45:51"
  }
  [1]=>
  array(2) {
    ["date"]=>
    string(19) "2017-07-05 14:45:51"
    [0]=>
    string(19) "2017-07-05 14:45:51"
  }
  [2]=>
  array(2) {
    ["date"]=>
    string(19) "2017-07-05 14:45:51"
    [0]=>
    string(19) "2017-07-05 14:45:51"
  }
  [3]=>
  array(2) {
    ["date"]=>
    string(19) "2017-07-05 14:45:51"
    [0]=>
    string(19) "2017-07-05 14:45:51"
  }
  [4]=>
  array(2) {
    ["date"]=>
    string(19) "2017-07-05 14:45:51"
    [0]=>
    string(19) "2017-07-05 14:45:51"
  }
}

UPDATE:

Example mysqli code, instead of PDO, to achieve the same output:

$db = new mysqli('localhost', 'root', 'xxxxx', 'test');
$result = $db->query("SELECT t.date FROM tryme t");

$rows = [];

while ( $row = $result->fetch_array() ) {
    $rows[] = $row;
}

var_dump($rows);

edited Jul 5, 2017 at 21:32

answered Jul 5, 2017 at 19:30

Paul T.

5,02712 gold badges48 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

WonderX Over a year ago

it is working fine, what is the problem with mysql function if I want to use it instead of PDO !

Paul T. Over a year ago

The mysql_* functions are considered deprecated, and should no longer be used. However, the mysqli_* can be used instead of PDO. Using mysqli should give the same results as my output.

WonderX Over a year ago

Thanks alot, got it now +1

Paul T. Over a year ago

So what did you do differently, for others that might cross this answer?

WonderX Over a year ago

I amend the subject and adding the solution

Collectives™ on Stack Overflow

mysql date is different when print from php

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related