User specific timezone

Derokorian

Hi guys,

I'm creating a site which will (hopefully) have a lot of users and I want them to be able to display datetimes in their timezone. So right now the first thing I do after connecting is issue a SET time_zone = "+00:00" query to mysql. I also have a date_default_timezone_set('UTC'); near the beginning. Then I use DateTime to output all the dates that are stored in mysql like so:

// Set TZ for internal calculations
date_default_timezone_set('UTC');

// Connect to mysql
	try {
		$this->PDO = new PDO(strtolower(DBENGINE).':host='.DBHOST.';dbname='.DBNAME.';',DBUSER,DBPASS);
		$this->PDO->exec('SET time_zone = "+00:00"');
	} catch (PDOException $e) {
		trigger_error('Unable to connect via PDO');
		exit;
	}

// Then in my config I have
define(  'TZ',    'America/New_York');


// Then I'm outputting dates like this
	$date = DateTime::createFromFormat('Y-m-d H:i:s',$comment['date']);
	$tz = isset($_SESSION['timezone']) ? new DateTimeZone($_SESSION['timezone']) : new DateTimeZone(TZ);
	$date->setTimezone($tz);
	echo $date->format('Y-m-d \a\t H:i');

My question to you guys is this: Is this the best way to handle user specific time zone settings? If not can you tell me why not and point me in the direction of a manual page or the like that tells the best way? It seems to work, if I change my timezone on my UserCP all the output changes accordingly properly. When I post a new post it shows up as the right time, but I'm worried this isn't the most efficient way.

Thanks in advance 😃

bradgrafelman

I'd probably use TIMESTAMP columns instead and then set the proper time zone for the SQL session. That way, MySQL will silently handle all of the conversions for you automatically.

Derokorian

Hmm, so the timezone column of the user table would hold -05:00 for me instead of America/New_York ? But what happens during Daylight Saving Time when its actually -04:00 how would I handle that? As it is now DateTime will take DST into account when specifying a TimeZone will it not?

bradgrafelman

I wouldn't choose '-05:00', I would prefer using 'US/Eastern' (which wouldn't change for DST since it's not EST/EDT specific).

EDIT: I guess I should mention that the above assumed your MySQL server was setup with timezone information.

Derokorian

Hmm

MySQL Manual wrote:
Named time zones can be used only if the time zone information tables in the mysql database have been created and populated.

I can't query this table, access denied for my user. And it says unrecognized time zone when I try to used named time zone. I may be stuck doing it in PHP.