Skip to content

Loading…

DBAL-33: Doctrine2 fails handling microseconds from PostgreSQL record #1515

Closed
doctrinebot opened this Issue · 6 comments

2 participants

@doctrinebot

Jira issue originally created by user jantichy:

A column in database may be defined as both TIMESTAMP WITH TIMEZONE and TIMESTAMP WITHOUT TIMEZONE. If I insert a new value directly to the database through NOW() function, the value is stored including microseconds.

But then, when I am trying to load the record to Doctrine entity, following exception is thrown:


Doctrine\DBAL\Types\ConversionException

Could not convert database value "2010-07-17 15:29:57.762+02" to Doctrine Type datetimetz

File: C:\dev\etrener\library\Doctrine\DBAL\Types\ConversionException.php Line: 46


The same with both datetime and datetimetz columns.

The problem is probably in PostgreSqlPlatform::getDateTimeTzFormatString(), where is the following row:
public function getDateTimeTzFormatString()

{ return 'Y-m-d H:i:sO'; }

But PostgreSQL stores timestamps with microseconds, so format should be maybe something like:

{ return 'Y-m-d H:i:s.uO'; }

The problem is already posted in http://www.doctrine-project.org/jira/browse/[DBAL-22](http://www.doctrine-project.org/jira/browse/DBAL-22)?focusedCommentId=13574&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_13574, but that issue is already closed so maybe it was overlooked already then.

Thank you for fix!

@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

Issue was closed with resolution "Fixed"

@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 doctrinebot added the Bug label
@beberlei beberlei was assigned by doctrinebot
@doctrinebot doctrinebot added this to the 2.0.0-BETA3 milestone
@doctrinebot doctrinebot closed this
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.