1

I searched many links but so far I coudn`t find a solution to my problem.

I have a web hosting on Go Daddy, and it is returning different times for PHP and MySQL:

  • PHP Datetime =>13/02/2018 17:10:50
  • MySQL Datetime =>13/02/2018 12:10:50

I've already set my PHP timezone to:

date_default_timezone_set('America/Sao_Paulo');

Would anyone know a way to adjust the MySQL time?

Thanks

Syscall
  • 19,327
  • 10
  • 37
  • 52
Marcos Felipe
  • 100
  • 13
  • 4
    MySQL's probably using UTC like it's supposed to. Keep your times in UTC in the database. Don't use local time. You'll regret it. Time should be a function of the user, of their position in the world at the time they're looking at your data, not where the database happens to be geographically located. – tadman Feb 13 '18 at 19:16
  • 1
    @tadman is right. Many frameworks uses this strategy, Drupal by example, stores all times in UTC. – Syscall Feb 13 '18 at 19:19
  • Thanks for your answer tadman. But setting my default timezone in PHP shouldn't convert the time to it? – Marcos Felipe Feb 13 '18 at 19:21
  • @MarcosFelipe (You should use the `@` sign before the user name, without they never receive your message.) – Syscall Feb 13 '18 at 19:42

2 Answers2

1

I found my solution at this link:

https://www.sitepoint.com/synchronize-php-mysql-timezone-configuration/

Actually, I synchronized my PHP and MySQL timezones. Here's the code:

define('TIMEZONE', 'America/Sao_Paulo');
date_default_timezone_set(TIMEZONE);

$now = new DateTime();
$mins = $now->getOffset() / 60;

$sgn = ($mins < 0 ? -1 : 1);
$mins = abs($mins);
$hrs = floor($mins / 60);
$mins -= $hrs * 60;

$offset = sprintf('%+d:%02d', $hrs*$sgn, $mins);

// I already have a connection function
$return = pdo_mysql("SET time_zone='$offset';");
Marcos Felipe
  • 100
  • 13
  • 1
    This is what we call "Gambiarra". If on the future you work with multiple timezones, you will have troubles. Also at every mysql connection you will need to set the timezone manually, adding one unecessary query to every connection. You should at least set the timezone on your mysql server configuation instead. – Elias Soares Feb 13 '18 at 23:14
  • @EliasSoares. But what is the difference setting my timezone in the DB or application? In any case I'd need to set the user location and apply it to PHP and DB. Is there another way to work around it? "Gambiarra" was awesome... Obrigado brasuca – Marcos Felipe Feb 14 '18 at 13:50
  • The main difference between setting the timezone in application is overhead. You're doing a unnecessary query. Obviously that this only applies if you have access to change mysql configuration file. If you use the `TIMESTAMP` type, you will not have this problem since it stores the timezone, and when you retrieve the data and parse it, it will be the exact same time that you stored, always. Abraço! :) – Elias Soares Feb 14 '18 at 14:55
  • Understood. I`ll take a look and in my next applications try to approach in this way. Thanks for your help @EliasSoares. Abraço! – Marcos Felipe Feb 14 '18 at 15:10
0

This is because the MySQL doesn't store timezone information on DATETIME type.

Probably your PHP script is sending a ISO8601 date string with the UTC+Timezone, so MySQL ignores the Timezone and stores only the UTC.

To avoid this, you can use the TIMESTAMP type that keeps track of the timezone.

Elias Soares
  • 9,884
  • 4
  • 29
  • 59