Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-33

Doctrine2 fails handling microseconds from PostgreSQL record

    Details

    • Type: Bug Bug
    • Status: 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

        Hide
        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
        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
        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
        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
        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ý
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: