Databases provide 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 configured by timezone name, e.g.
By default, MySQL server doesn't support named timezones, 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.
This will make Dbal fully functional, althought some SQL queries and
expressions may not return corectly calculated results, e.g. functions which
calculate two-date oprations directly in database –
Basically, there are three possibilities what database could do with time
stamps ((the following text use the term
time stamp in the meaning
of identification of specific time)):
TIMEDIFF()may give wrong result;
The following table presents 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:
Nextras 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;
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;