Also, there's no way to check if a convert From and To PHP/Database worked correctly. Maybe we should introduce an exception when a DBAL\Type conversion fails, otherwise, there's no way to figure it out what happened.

Benjamin Eberlei
added a comment - 14/Jun/10 3:20 PM throwing an exception is a no-go in my opinion, it would completely cripple an application if there is some change in the DB.
There are no checks if an integer field is really in int, or if the db has a varchar field making the (int) conversion fail. This should be ignored and is responsibility of the user to get right.
I am looking into the format, however we have several unit-tests for this working correctly, i am a bit confused.

We should change the default behaviour in Postgres (and Oracle) to be WITHOUT TIMEZONE, since this is the 90% use-case. Additionally it may create less bugs when we don't fiddle with the Timezone used for creation.

Benjamin Eberlei
added a comment - 17/Jun/10 4:50 PM I changed the title to reflect the real issue of this ticket, using Postgresql with WITH TIMEZONE is rather problematic with regard to DateTime and DateTimeZone Handling inside PHP, see the following code snippets ( http://pastie.org/1009033 ) and Dericks post ( http://derickrethans.nl/storing-date-time-in-database.html ).
We should change the default behaviour in Postgres (and Oracle) to be WITHOUT TIMEZONE, since this is the 90% use-case. Additionally it may create less bugs when we don't fiddle with the Timezone used for creation.

Benjamin Eberlei
added a comment - 21/Jun/10 2:28 PM Ok we also discussed errors when conversion failed and added a ConversionException. We also implemented a new type DateTimeTz.
This is both currently in my feature branch: http://github.com/doctrine/dbal/tree/DBAL-22

This is a copy of a mail going to the doctrine-dev and doctrine-user lists some minutes ago:

Hello Doctrine 2 + Postgres and Oracle Users,

Both Postgres and Oracle currently save the Date Offset for DateTime
instances they are handling from Doctrine 2. However Date Offsets should
not be confused with Timezones and this can cause considerable issues
with transitions, modifications and comparisons of dates.

As a result we have to change the DateTime type implementations of
Oracle/Postgres for Beta 3 to reduce the risk of users running into date
calculation problems.

Required changes inside Doctrine DBAL Package:

The column create statement on both platforms will be changed from
"TIMESTAMP(0) WITH TIME ZONE" to "TIMESTAMP(0) WITHOUT TIME ZONE".

The supported PHP date format will change from "Y-m-d H:i:sO" to
"Y-m-d H:i:s".

Schema-Tool will automatically detect dates with offset retrieved from
the Database as "DateTimeTz" types.

Wrongly converted date values will throw a "Doctrine\DBAL\Types
\ConversionException". This will stop any non-migrated from running, but
more importantly from corrupting your data.

What does that mean to you as a user?

POSTGRES:

There are two solutions if you use Postgres:

1. The easy way out: We will introduce a new Type "DateTimeTz", which
will keep backwards compability. You will however have to deal with the
Timezone issues yourself. One way is by setting your application
"date.timezone" ini variable to be an offset instead of a timezone on
your server. You have to make transition calculations yourself in this
case.

2. Converting the columns: When upgrading from Beta 2 to the master or
Beta 3 the type will change its behavior, conversions from a Beta 2 WITH
TIME ZONE column to the new type will fail, leading to a
"ConversionException" being thrown. However Schema-Tool will recognize
the changes automatically and ask you to convert the column. First tests
of me showed that converting the TIMESTAMP column from WITH to WITHOUT
timezone works, it even corrects all dates for the offsets to UTC.

ORACLE:

Oracle does not permit changing the types when there is already data in
it (guessing from the preliminary tests I made).
You have to switch all your entity fields using the "DateTime" type to
use the new "DateTimeTz" type otherwise you will experience an
"ConversionException" being thrown.

Benjamin Eberlei
added a comment - 21/Jun/10 5:40 PM This is a copy of a mail going to the doctrine-dev and doctrine-user lists some minutes ago:
Hello Doctrine 2 + Postgres and Oracle Users,
Both Postgres and Oracle currently save the Date Offset for DateTime
instances they are handling from Doctrine 2. However Date Offsets should
not be confused with Timezones and this can cause considerable issues
with transitions, modifications and comparisons of dates.
As a result we have to change the DateTime type implementations of
Oracle/Postgres for Beta 3 to reduce the risk of users running into date
calculation problems.
Required changes inside Doctrine DBAL Package:
The column create statement on both platforms will be changed from
"TIMESTAMP(0) WITH TIME ZONE" to "TIMESTAMP(0) WITHOUT TIME ZONE".
The supported PHP date format will change from "Y-m-d H:i:sO" to
"Y-m-d H:i:s".
Schema-Tool will automatically detect dates with offset retrieved from
the Database as "DateTimeTz" types.
Wrongly converted date values will throw a "Doctrine\DBAL\Types
\ConversionException". This will stop any non-migrated from running, but
more importantly from corrupting your data.
What does that mean to you as a user?
POSTGRES:
There are two solutions if you use Postgres:
1. The easy way out: We will introduce a new Type "DateTimeTz", which
will keep backwards compability. You will however have to deal with the
Timezone issues yourself. One way is by setting your application
"date.timezone" ini variable to be an offset instead of a timezone on
your server. You have to make transition calculations yourself in this
case.
2. Converting the columns: When upgrading from Beta 2 to the master or
Beta 3 the type will change its behavior, conversions from a Beta 2 WITH
TIME ZONE column to the new type will fail, leading to a
"ConversionException" being thrown. However Schema-Tool will recognize
the changes automatically and ask you to convert the column. First tests
of me showed that converting the TIMESTAMP column from WITH to WITHOUT
timezone works, it even corrects all dates for the offsets to UTC.
ORACLE:
Oracle does not permit changing the types when there is already data in
it (guessing from the preliminary tests I made).
You have to switch all your entity fields using the "DateTime" type to
use the new "DateTimeTz" type otherwise you will experience an
"ConversionException" being thrown.
Planed Schedule for the changes:
The complete changes are currently in a DBAL feature branch on Github
http://github.com/doctrine/dbal/tree/DBAL-22
Merge into the DBAL project this week. This WONT affect you using the
ORM just yet, its a DBAL change only!
The ORM Master on Github will still be linked against the DBAL Beta 2
via a Git Submodule (doctrine2/lib/vendor/doctrine-dbal).
In the timestamp between the DBAL Beta3 and the ORM Beta3 release we
will integrate the changes into the ORM package also.
I will send an additional notice to the lists when we will bump the ORM
dependency on the DBAL.
Sorry for the inconvenience regarding this issue, but we feel very
strongly about making this change. This will ultimately solve many
subtle issues that would have popped up here and there.
Thanks goes to Elisabeth Smith for bringing this issue to the table and
to Derick Rethans who helped us understand why timezones/offset in the
database are a pain to work with.
greetings,
Benjamin

Hi, on master branch of DBAL package in PostgreSqlPlatform::getDateTimeTzFormatString() I see this:

public function getDateTimeTzFormatString()

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

But PostgreSQL stores timestamps with microseconds, so format should be more likely:

public function getDateTimeTzFormatString()

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

Yes, Doctrine converts DateTimeTz from PHP value to database value without microseconds, but if I will use some database function (for example now()) for get timestamp and I will store it to the database directly and than I will read this values through Doctrine, it will cause an error.

Is there any reason for not using default PostgreSQL format of timestamp with microseconds?

Václav Novotný
added a comment - 13/Jul/10 7:38 AM - edited Hi, on master branch of DBAL package in PostgreSqlPlatform::getDateTimeTzFormatString() I see this:
public function getDateTimeTzFormatString()
{
return 'Y-m-d H:i:sO';
}
But PostgreSQL stores timestamps with microseconds, so format should be more likely:
public function getDateTimeTzFormatString()
{
return 'Y-m-d H:i:s.uO';
}
Yes, Doctrine converts DateTimeTz from PHP value to database value without microseconds, but if I will use some database function (for example now()) for get timestamp and I will store it to the database directly and than I will read this values through Doctrine, it will cause an error.
Is there any reason for not using default PostgreSQL format of timestamp with microseconds?