DateTime
Database engines provide different types for storing date-times. Also, the type naming is often misleading. This documentation chapter covers the basics and Dbal’s solution to the datetime & timezone handling.
Generally, we recognize two 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;
-
DateTime - is an exact timestamp on the time-line; example: timestamp of the meeting start in a calendar; this type is also referred as an
Instanttype.- UTC DateTime - having this type represented in UTC means we don’t know an exact context where it happened; it could be in the day or in the night;
- Zoned DateTime - is an exact timestamp on the time-line plus an additional context of specific time-zone; either we use reader’s timezone or timezone of the location where the timestamp “happened”; example: presenting an online streaming event start - since it is pretty usual that this event will be watched from multiple places, we need add reader’s timezone context to the stored UTC DateTime.
The following table presents a matrix of available DB date-time types:
| Local DateTime no timezone handling |
DateTime timezone conversion |
DateTime timezone stored |
|
|---|---|---|---|
| MySQL | datetime |
timestamp |
- |
| Postgres | timestamp |
timestamptz |
- |
| SQL Server | datetime, datetime2 |
- | datetimeoffset |
- no timezone handling: database stores the time-stamp and does not do any modification to it; this is the easiest solution, but brings a disadvantage: database cannot exactly diff two time-stamps, i.e. it may produce wrong results because day-light saving shift is needed but db does not know which zone to use for the calculation;
- timezone conversion: database stores the time-stamp unified in UTC and reads it in connection’s timezone;
- timezone stored: database does not do any conversion, it just stores the timezoned timestamp and returns it back;
Dbal offers a connection time zone configuration option (connectionTz) that defines the timezone for database connection communication; it equals to PHP’s current default timezone by default. This option is configured by a timezone name, e.g. Europe/Prague string.
Dbal comes with two query modifiers:
| Type | Modifier | Description |
|---|---|---|
| local datetime | %ldt |
passes DateTime(Interface) object as it is, without any timezone conversion and identification; formerly known as datetime simple (%dts) |
| datetime | %dt |
converts DateTime(Interface) object to connection timezone; |
MySQL
Writing
| Type | Modifier | Comment |
|---|---|---|
| local datetime | %ldt |
timezone (offset) is removed |
| datetime | %dt |
value is converted to connection timezone and timezone offset is removed if properly stored to timestamp column type |
Reading
| Type | Column Type | Comment |
|---|---|---|
| local datetime | datetime |
value is converted into application timezone |
| datetime | timestamp |
value is interpreted in connection timezone and converted into application timezone |
Connection Time Zone
By default, MySQL server does not support named timezones, see the setup chapter how to configure them. Still, there is a possibility to pass only a timezone offset configuraion, e.g. +03:00, but this is not ideal. Use rather 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 calculating two-date operations directly in the database - TIMEDIFF, ADDDATE.
Postgres
Writing
| Type | Modifier | Comment |
|---|---|---|
| local datetime | %ldt |
timezone (offset) is removed |
| datetime | %dt |
value is converted to connection timezone and timezone offset is removed if properly stored to timestamptz column type |
Reading
| Type | Column Type | Comment |
|---|---|---|
| local datetime | timestamp |
value is converted into application timezone |
| datetime | timestamptz |
value is converted into application timezone |
SQL Server
Writing
| Type | Modifier | Comment |
|---|---|---|
| local datetime | %ldt |
timezone (offset) is removed |
| datetime | %dt |
no timezone conversion is done and the timezone offset is stored in datetimeoffset db type |
Reading
| Type | Column Type | Comment |
|---|---|---|
| local datetime | datetime |
value is converted into application timezone |
| datetime | datetimeoffset |
value is read with timezone offset and no further modification is done - i.e. no application timezone conversion happens |