DateTime TimeZones Support

Database engines provide different types for storing date-times. Also, the type naming is often misleading. This documentation page covers the basics and Dbal's solution to the datetime & timezone problem.

Generally, we recognize three types of date time types:

Local DateTime – it is a date time which has not an exact position on the time-line; simply, we do not know in which time zone the event happened, therefore we consider the information as a local; example: date time when the school year begins, since the country may be across more the timezones, this type of information may be stored as a local date time, i.e. striping the “exactness” may be an advantage here;
UTC DateTime – is an exact timestamp on the time-line; example: timestamp of the meeting start in a calendar;
Zoned DateTime – is an exact timestamp on the time-line plus an added context of specific time-zone; either we use reader's timezone or timezone of the location when the timestamp “happened”; example: presenting an online streaming event start – since it is pretty usual that this event will be watched from multiple times, we need add reader's timezone context to the stored UTC DateTime.

The following table presents a matrix of available data-time types and their behavior:

	Local DateTime	UTC DateTime	Zoned DateTime
	no timezone handling	timezone conversion	timezone stored
MySQL	`datetime`	`timestamp`	–
Postgres	`timestamp`	`timestamptz`	–
SQL Server	`datetime`, `datetime2`	–	`datetimeoffset`

no timezone handling: this is straightforward attitude; database just store your time-stamp and does not do any modification to the timestamp; this is the easiest solution, but brings a disadvantage: database cannot exactly diff two timestamps, i.e. it maz produce wrong results because day-light saving shift`
timezone conversion: database stores the time stamp unified in UTC and for MySQL, it converts the timestamp to connection's timezone for every read and write;
timezone stored: database does not do any conversion, it just stores the timezoned timestamp and returns it back;

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 configured by timezone name, e.g. Europe/Prague string.

By default, MySQL server doesn't support named timezones, see the setup chapter how to configure them. Still, there is a possibility to pass only a timezone offset, e.g. +03:00, but this is not ideal. Use magic auto-offset value that will be dynamically converted to the current PHP's timezone offset.

This will make Dbal fully functional, although some SQL queries and expressions may not return correctly calculated results, e.g. functions that calculate two-date operations directly in the database – TIMEDIFF, ADDDATE, etc.

Storing#

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 conversion and identification;

	Local DateTime	UTC DateTime	Zoned DateTime
	no timezone handling	timezone conversion	timezone stored
MySQL	`%dts`	`%dt`	–
Postgres	`%dts`	`%dt`	–
SQL Server	`%dts`	–	`%dt`

Reading#

Database drivers also correctly read the stored values and convert them into DateTimeImmutable instances:

MySQL driver interprets timestamp column in connection's timezone and converts it to PHP's default timezone;
Postgres driver reads timestamptz (timezoned timestamp) and converts it to PHP's default timezone;
SQL server driver reads datetimeoffset (timezoned timestamp) and does not do any timezone conversion;

Dbal

Documentation