Good day. Faced the problem of displaying the date. The database in the files table has a date column in the timestamp format. It means 2017-07-20 09:32:23

When I do a sample

$sql = "SELECT * from files where id= $i"; // $i итерационная переменная $stmt = $db->prepare($sql); $result = $stmt->fetch(PDO::FETCH_ASSOC); $date=date('F/j/Y',strtotime($result['date']));

And I’m echo "<p>Дата:'$date'</p>";

Displays 'January / 1/1970'

Tell me, please, where was I wrong? Can you tell me how to format the timestamp correctly so that it displays the exact time the file was uploaded to the server? When tried $date=$result['date']; , echo didn’t produce anything except quotes. Also experimented with sql query: id = 1, SELECT * - nothing helped.

Used the date () function description

  • Format the date on the MySQL side w3schools.com/sql/func_mysql_date_format.asp - Mike
  • one
    Check if $result['date'] is exactly the date you need - Al Mr
  • Used $ sql = 'SELECT DATE_FORMAT (date, "% W% M% e% Y") FROM files'; but it did not help - abrgpro
  • In the result array, for some reason, it is empty for any query .. At least it’s understandable why it just outputs a quote instead of a date. - abrgpro
  • and plus check if the values ​​in the database are empty - Vasil Bodnaruk

1 answer 1

 $date = date('Ymd H:i:s', strtotime($result['date'])); //H:i:s - 24ти часовой формат //h:i:s - 12х часовой формат // strtotime - если не уверены в фомате поля date

UPD:

To the question of time zone. Look, what is your default server default (in php.ini)? This can be done via phpinfo() , or emit date_default_timezone_get(); . And if not yours, then set the necessary (for example, Ukraine) date_default_timezone_set('europe/kiev')

  • Displays Notice: A non well formed numeric value encountered in, if you use $ result ['date'] [0] displays 1970-01-01 12:00:00, if you just output echo $ result ['date']; displays normal 2017-07-20 09:32:23. It is still not clear why formatting does not work and how to change the time zone. Now the whole construction looks like this: $sql = 'SELECT * FROM files where id=1'; $stmt = $db->query($sql); $result = $stmt->fetch(PDO::FETCH_ASSOC); echo $result['date']; $sql = 'SELECT * FROM files where id=1'; $stmt = $db->query($sql); $result = $stmt->fetch(PDO::FETCH_ASSOC); echo $result['date']; - abrgpro
  • It seems that the date field in the table has a text type and not a date / time. From that and unexpected exhaust. Corrected the answer for your version. - Kirill Korushkin
  • It really works, but the type is the same date / time screenshot . Thank you for help. Unfortunately I can not. My project is on free hosting, so I’ve got to be wise with changing the time zone by scripts after getting it from the database. - abrgpro
  • php.net/manual/ru/function.date-default-timezone-set.php here and about timezone and your vorning. - Kirill Korushkin
  • Thank you so much. It turned out to solve your problem like this date_default_timezone_set('Europe/Moscow'); $date = new DateTime(); $date->getTimestamp(); $date=date('Ymd H:i:s'); $sql = "INSERT INTO files(name, user_name, date) VALUES (:name, :user_name, '$date')"; date_default_timezone_set('Europe/Moscow'); $date = new DateTime(); $date->getTimestamp(); $date=date('Ymd H:i:s'); $sql = "INSERT INTO files(name, user_name, date) VALUES (:name, :user_name, '$date')"; - code when adding a file. It remains only to figure out how to get the user's time zone and shove it into date_default_timezone_set. - abrgpro