Edit

DateTime TimeZones Support

Databases have different types for storing datetimes. Also, the type naming is often misleading. This documentation page covers the basics and Dbal's solution to the datetime & timezone problem.

Dbal offers connection time zone configuration option (connectionTz) which defines the timezone for database connection communication; by default it equals to PHP's current default timezone. This option is configure by timezone name, e.g. Europe/Prague string. However, by default, MySQL server doesn't support this, see the setup chapter how to configure them. Still, there is a possiblity to pass only a timezone offset, e.g. +03:00, but this is not ideal if you server is in timezone with daylight saving time-shift. Use magic auto-offset value that will be dynamically converted to the current PHP's timezone offset.

Dbal comes with two query modifiers:

• %dt (as datetime): converts DateTime object to connection timezone;
• %dts (as datetime simple): pass DateTime object as it is, without any timezone identification;

Basically, there are three possibilities what database could do with time stamps:

• no timezone handling: this is straightforward attitude; database just store your date-time stamp and does not do any modification to handle time zones; this is the easiest solution, but brings a disadvantage: you cannot exactly diff two time stamps in database, because database does not know anything about timezones – e.g. day-light saving shift, therefore functions like MySQL's TIMEDIFF() may give wrong result;
• timezone conversion: database stores the time stamp unified in UTC and for every operation converts the time-stamp to proper working (connection's) timezone;
• timezone stored: database do not do conversion, it just stores the timezone and returns it back with the timestamp;

In the following table there is matrix of available time stamp data types and their behavior:

Let's describe timezone conversion for specific databases:

• MySQL:
  • writing: passed time stamp is interpreted in connection timezone; if time stamp has timezone defined, it is ignored and still interpreted in connection timezone;
  • reading: time stamp is returned in connection timezone without timezone identification;
• Postgres:
  • writing: passed time stamp can contain timezone, if not, it is interpreted in connection timezone;
  • reading: time stamp is returned in connection timezone with timezone identification;

Usage matrix#

• MySQL's datetime + Postgres' timestamp + SQL Server's datetime & datetime2:
  • use %dts for storing;
  • use %dt for storing if and only if you have stable PHP's & connection timezone, this may be true for vast majority of Dbal's usage;
  • value is read as it is, e.g. in PHP's timezone;
• MySQL's timestamp + Postgres timestamptz:
  • use %dt for storing;
  • use %dts for storing if and only if the value's timezone is the same as the connection timezone;
  • value is read in connection timezone and then converted into PHP's timezone;
• SQL Server's datetimeoffset:
  • use %dt for storing;
  • usage of %dts will strip the timezone;
  • value is read with the stored timezone (and not converted into PHP's timezone);