[#DBAL-33] Doctrine2 fails handling microseconds from PostgreSQL record

Doctrine DBAL

Doctrine2 fails handling microseconds from PostgreSQL record

Created: 17/Jul/10 11:03 AM Updated: 24/Jul/10 06:56 PM

Component/s:

None

Affects Version/s:

None

Fix Version/s:

2.0.0-BETA3

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

« Hide

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!

All

Comments

Change History

FishEye

Sort Order:

[ Permalink | « Hide ]

Benjamin Eberlei added a comment - 20/Jul/10 05:27 AM

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?

[ Permalink | « Hide ]

Benjamin Eberlei added a comment - 24/Jul/10 06:39 AM

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

[ Permalink | « Hide ]

Benjamin Eberlei added a comment - 24/Jul/10 11:44 AM - 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.

[ Permalink | « Hide ]

Benjamin Eberlei added a comment - 24/Jul/10 11:44 AM

++ 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');

[ Permalink | « Hide ]

Jan Tichý added a comment - 24/Jul/10 06:56 PM

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