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.
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:
TIMEDIFF()may give wrong result;
In the following table there is matrix of available time stamp data types and their behavior:
|no TZ handling||TZ conversion||TZ stored|
|SQL Server||datetime, datetime2||–||datetimeoffset|
Let's describe timezone conversion for specific databases:
timestamp+ SQL Server's
%dtfor storing if and only if you have stable PHP's & connection timezone, this may be true for vast majority of Dbal's usage;
%dtsfor storing if and only if the value's timezone is the same as the connection timezone;
%dtswill strip the timezone;