Syncing MySQL and PHP times

Question

I am trying to sync my PHP and MySQL times. Currently the times are different (outputs below):

'SELECT CURRENT_TIMESTAMP' returns '2013-03-16 13:14:28'
'date('Y-m-d g:i:s')'      returns '2013-03-16 10:14:28'

I have had success, reading from other users, on changing the MySQL time using SET time_zone='-7:00';. The problem is the time will be changing for different queries based on the user's location and I would rather not change the time_zone for every query.

Should I just set the time_zone once, making it the same time as the server, and then change the time based on the user's timezone in PHP? Or is there a better way to go about this?

Thank you in advance!

Answer 1

Should I just set the time_zone once, making it the same time as the server, and then change the time based on the user's timezone in PHP? Or is there a better way to go about this?

Yes. You also ought not to set timezones via their GMT zone because of daylight savings time. You can write yourself functions to do this conversion by setting date_default_timezone_set('UTC'); and then to your user's timezone.

$time = strtotime('2013-03-16 13:14:28'.' UTC');
echo date('Y-m-d g:i:s',$time); //2013-03-16 9:14:28

Another alternative is to store mysql times in "Unix Time" and then convert only once with PHP.