Fork me on GitHub

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. 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 ` string which 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:

  no TZ handling TZ conversion TZ stored
MySQL datetime timestamp
Postgres timestamp timestamptz
SQL Server datetime, datetime2 datetimeoffset

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);