Unmarshalling DateTime's when using different timezones

[bjornpost]

Our configuration:

• App server: date_default_timezone_set('Europe/Amsterdam');
• Database server: @@global.time_zone=UTC, @@session.time_zone=UTC

When unmarshalling datetimes, a DateTime object is created with just the datetime string returned by MySQL (i.e. 2015-06-30 15:38:29, which is +0000). As no TZ info is provided, PHP assumes the provided string is in the default_timezone, so 2015-06-30 15:38:29 becomes 2015-06-30 15:38:29+02:00. Whoops :-).

Right now, when connecting, I'm setting the correct timezone:

$p = \Pheasant::setup('mysql://user:pass@host/table');
$p->connection()->execute("SET time_zone='".date_default_timezone_get()."';");

• Is this a query we should run by default?
• Should we be less naive and provide the @@session.time_zone when unmarshalling DateTime's?

Thoughts?

[bjornpost]

Thoughts, @jud @lox?

[Jud]

@bjornpost I think this would be something to document, but not do automatically.

[bjornpost]

@Jud why not do this automatically? I think it reduces the chance of unwanted/unpredictable side effects?

[Jud]

Might be best to follow suit and do something like ActiveRecord's config.active_record.default_timezone, which may either be set to :utc or :local.

Could allow the option to be set when doing Pheasant::setup.

[lox]

That reminds me why I've always avoided mysql timestamps :)

I think @Jud's suggestion is the right approach, I'd have a timezone somewhere for the database connection (set it here https://github.com/lox/pheasant/blob/master/lib/Pheasant/Database/Mysqli/Connection.php#L125) and then use that to correctly unmarshal timestamp's into the right tz.

[bjornpost]

Closing this for now.