DBAL-33: Doctrine2 fails handling microseconds from PostgreSQL record

[doctrine]

DBAL-33: Doctrine2 fails handling microseconds from PostgreSQL record

[doctrinebot]

Comment created by @beberlei:

Both DateTime and DateTimeTz assume that TIMESTAMP(0) is the definition, not the TIMESTAMP that implicitly degrades to TIMESTAMP(6). I am not sure how to handle this, I find this Postgres DateTime stuff rather annoying :-)

Is there a global client side option for Postgres users to configure this?

[doctrinebot]

Comment created by @beberlei:

@jan: Did you use create the column manually yourself (TIMESTAMP WITHOUT TIMEZONE) or use the Doctrine Schema-Tool which defines TIMESTAMP(0) WITHOUT TIMEZONE.

[doctrinebot]

Comment created by @beberlei:

Fixed in DBAL Trunk, see the following section of the DBAL manual to understand the workaround for PostgreSQL TIMESTAMP( n ) types where n > 0.

ORM always creates TIMESTAMP(0), so this is handled as a legacy database schema.

[doctrinebot]

Comment created by @beberlei:

<ins></ins> PostgreSQL

<ins></ins>+ 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 save a value with microseconds = 0.
PostgreSQL will return this value in the format:

    2010-10-10 10:10:10 (Y-m-d H:i:s)

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

    2010-10-10 10:10:10.123456 (Y-m-d H:i:s.u)

Using the DateTime, DateTimeTz or Time type with microseconds enabled columns
can lead to errors because internally types expect the exact format 'Y-m-d H:i:s'
in combination with `DateTime::createFromFormat()`. This method is twice a fast
as passing the date to the constructor of `DateTime`.

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

* DateTime to `TIMESTAMP(0) WITHOUT TIME ZONE`
* DateTimeTz to `TIMESTAMP(0) WITH TIME ZONE`
* Time to `TIME(0) WITHOUT TIME ZONE`

If you do not let Doctrine create the date column types and rather use types with microseconds
you have replace the "DateTime", "DateTimeTz" and "Time" types with a more liberal DateTime parser
that detects the format automatically:

    [php]
    use Doctrine\DBAL\Types\Type;

    Type::overrideType('datetime', 'Doctrine\DBAL\Types\VarDateTime');
    Type::overrideType('datetimetz', 'Doctrine\DBAL\Types\VarDateTime');
    Type::overrideType('time', 'Doctrine\DBAL\Types\VarDateTime');

[doctrinebot]

Comment created by jantichy:

@benjamin: Yes, I have created the column manualy directly in database using my own CREATE TABLE definition.

[doctrinebot]

Issue was closed with resolution "Fixed"

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.