DateTime, DateTimeTz and Time Types

Postgres has a variable return format for the datatype TIMESTAMP(n)and TIME(n) if microseconds are allowed (n > 0). Whenever you savea value with microseconds = 0, PostgreSQL will return this value inthe format:

2010-10-1010:10:10(Y-m-dH:i:s)

However if you save a value with microseconds it will return thefull representation:

2010-10-1010:10:10.123456(Y-m-dH:i:s.u)

Using the DateTime, DateTimeTz or Time type (and immutable variants) with microsecondsenabled columns can lead to errors because internally types expectthe exact format ‘Y-m-d H:i:s’ in combination withDateTime::createFromFormat(). This method is twice as fast aspassing the date to the constructor ofDateTime.

This is why Doctrine always wants to create the time related typeswithout microseconds:

• DateTime toTIMESTAMP(0)WITHOUTTIMEZONE

• DateTimeTz toTIMESTAMP(0)WITHTIMEZONE

• Time toTIME(0)WITHOUTTIMEZONE

If you do not let Doctrine create the date column types and ratheruse types with microseconds you have replace the “DateTime”,“DateTimeTz” and “Time” types (and immutable variants) with a moreliberal DateTime parser that detects the format automatically:

useDoctrine\DBAL\Types\Type;Type::overrideType('datetime','Doctrine\DBAL\Types\VarDateTimeType');Type::overrideType('datetimetz','Doctrine\DBAL\Types\VarDateTimeType');Type::overrideType('time','Doctrine\DBAL\Types\VarDateTimeType');Type::overrideType('datetime_immutable','Doctrine\DBAL\Types\VarDateTimeImmutableType');Type::overrideType('datetimetz_immutable','Doctrine\DBAL\Types\VarDateTimeImmutableType');Type::overrideType('time_immutable','Doctrine\DBAL\Types\VarDateTimeImmutableType');

Timezones and DateTimeTz

Postgres does not save the actual Timezone Name but UTC-Offsets.The difference is subtle but can be potentially very nasty. DerickRethans explains it very wellin a blog post of his.

DateTimeTz

MySQL does not support saving timezones or offsets. The DateTimeTztype therefore behaves like the DateTime type.

Buffered Queries and Isolation

Be careful if you execute aSELECT query and do not iterate over thestatements results immediately.UPDATE statements executed before iterationaffect only the rows that have not been buffered into PHP memory yet. Thisbreaks the SERIALIZABLE transaction isolation property that SQLite supposedlyhas.

DateTime

Unlike most database management systems, Sqlite does not convert supplieddatetime strings to an internal storage format before storage. Instead, Sqlitestores them as verbatim strings (i.e. as they are entered) and expects the userto use theDATETIME() function when reading data which then converts thestored values to datetime strings.Because Doctrine is not using theDATETIME() function, you may end up with“Could not convert database value … to Doctrine Type datetime.” exceptionswhen trying to convert database values to\DateTime objects using

\Doctrine\DBAL\Types\Type::getType('datetime')->convertToPhpValue(...)

DateTimeTz

Sqlite does not support saving timezones or offsets. The DateTimeTztype therefore behaves like the DateTime type.

Reverse engineering primary key order

SQLite versions < 3.7.16 only return that a column is part of the primary key,but not the order. This is only a problem with tables where the order of thecolumns in the table is not the same as the order in the primary key. Tablescreated with Doctrine use the order of the columns as defined in the primarykey.

DateTimeTz

DB2 does not save the actual Timezone Name but UTC-Offsets. Thedifference is subtle but can be potentially very nasty. DerickRethans explains it very wellin a blog post of his.

DateTimeTz

Oracle does not save the actual Timezone Name but UTC-Offsets. Thedifference is subtle but can be potentially very nasty. DerickRethans explains it very wellin a blog post of his.

OCI-LOB instances

Doctrine DBAL always requests CLOB columns as strings, so that you asa developer never get access to theOCI-LOB instance. Since weare using prepared statements for all write operations inside theORM, using strings instead of theOCI-LOB does not cause anyproblems.

Unique and NULL

Microsoft SQL Server takes Unique very seriously. There is onlyever one NULL allowed contrary to the standard where you can havemultiple NULLs in a unique column.

DateTime, DateTimeTz and Time Types

SQL Server has a variable return format for the datatype DATETIME(n)if microseconds are allowed (n > 0). Whenever you savea value with microseconds = 0.

If you do not let Doctrine create the date column types and ratheruse types with microseconds you have replace the “DateTime”,“DateTimeTz” and “Time” types (and immutable variants) with a moreliberal DateTime parser that detects the format automatically:

useDoctrine\DBAL\Types\Type;Type::overrideType('datetime','Doctrine\DBAL\Types\VarDateTime');Type::overrideType('datetimetz','Doctrine\DBAL\Types\VarDateTime');Type::overrideType('time','Doctrine\DBAL\Types\VarDateTime');Type::overrideType('datetime_immutable','Doctrine\DBAL\Types\VarDateTimeImmutableType');Type::overrideType('datetimetz_immutable','Doctrine\DBAL\Types\VarDateTimeImmutableType');Type::overrideType('time_immutable','Doctrine\DBAL\Types\VarDateTimeImmutableType');