Doctrine DBAL

Doctrine2 fails handling microseconds from PostgreSQL record

Details

  • Type: Bug Bug
  • Status: Resolved Resolved
  • Priority: Major Major
  • Resolution: Fixed
  • Affects Version/s: None
  • Fix Version/s: 2.0.0-BETA3
  • Component/s: None
  • Labels:
    None
  • Environment:
    Doctrine - GIT trunk (night build from 2010-07-17), Windows 7, PHP 5.3.2, PostgreSQL 8.4.3, Apache 2.2.15

Description

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?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!

Activity

Ascending order - Click to sort in descending order
Hide
Permalink
Benjamin Eberlei added a comment -

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?

Show
Benjamin Eberlei added a comment - 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?
Hide
Permalink
Benjamin Eberlei added a comment -

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

Show
Benjamin Eberlei added a comment - @Jan: Did you use create the column manually yourself (TIMESTAMP WITHOUT TIMEZONE) or use the Doctrine Schema-Tool which defines TIMESTAMP(0) WITHOUT TIMEZONE.
Hide
Permalink
Benjamin Eberlei added a comment - - edited

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.

Show
Benjamin Eberlei added a comment - - edited 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.
Hide
Permalink
Benjamin Eberlei added a comment - 
++ PostgreSQL

+++ 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');
Show
Benjamin Eberlei added a comment - ++ PostgreSQL +++ 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');
Hide
Permalink
Jan Tichý added a comment -

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

Show
Jan Tichý added a comment - @Benjamin: Yes, I have created the column manualy directly in database using my own CREATE TABLE definition.

People

  • Assignee:
    Benjamin Eberlei
    Reporter:
    Jan Tichý
Vote (0)
Watch (2)

Dates

  • Created:
    Updated:
    Resolved: