Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-22

Saving UTC Offset in Postgres DATETIME messes with PHP DateTime and Timezones

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 2.0.0-BETA3
    • Component/s: Platforms
    • Labels:
      None

      Description

      The DateTime type is currently unusable in PostgreSQL due to a wrong parsing format.

      PostgreSqlPlatform::getDateTimeFormatString() should be changed from:

      Y-m-d H:i:sO

      to

      Y-m-d H:i:s.uO

      According to http://twitter.com/auroraeosrose/statuses/16154268629

      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.

        Activity

        Hide
        Benjamin Eberlei added a comment -

        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.

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

        The question is rather, should we deprecate the usage of timezones alltogether? This is a good read on the topic: http://derickrethans.nl/storing-date-time-in-database.html

        Show
        Benjamin Eberlei added a comment - The question is rather, should we deprecate the usage of timezones alltogether? This is a good read on the topic: http://derickrethans.nl/storing-date-time-in-database.html
        Hide
        Benjamin Eberlei added a comment -

        ok the current state is, we use "TIMESTAMP(0) WITH TIMEZONE", which matches the currently given format "Y-m-d H:i:sO"

        Show
        Benjamin Eberlei added a comment - ok the current state is, we use "TIMESTAMP(0) WITH TIMEZONE", which matches the currently given format "Y-m-d H:i:sO"
        Hide
        Elizabeth M Smith added a comment -

        The "TIMESTAMP(0) WITH TIMEZONE" is a very UNCOMMON time field

        Usually timstamp without time zone is used

        Also the difference between integer field is really in int and db has a varchar field making the (int) is different for one reason

        The timestamp is returning a datetime object - not a scalar

        What happens when you try to use something in PHP that's not an object as an object? It fatal errors

        If the integer field is not really an integer when you get your data back from the db and you attempt to use it, PHP is not going to throw a fatal error at you. Your application might be angry, but that's another story.

        I would almost rather you simply use strtotime and pass that to the datetime class for postgresql since it will work on all 'versions" of postgresql's timestamp, regardless if it has microseconds or timezone information. Then you're not locking all users of doctrine into using one timestamp format.

        Show
        Elizabeth M Smith added a comment - The "TIMESTAMP(0) WITH TIMEZONE" is a very UNCOMMON time field Usually timstamp without time zone is used Also the difference between integer field is really in int and db has a varchar field making the (int) is different for one reason The timestamp is returning a datetime object - not a scalar What happens when you try to use something in PHP that's not an object as an object? It fatal errors If the integer field is not really an integer when you get your data back from the db and you attempt to use it, PHP is not going to throw a fatal error at you. Your application might be angry, but that's another story. I would almost rather you simply use strtotime and pass that to the datetime class for postgresql since it will work on all 'versions" of postgresql's timestamp, regardless if it has microseconds or timezone information. Then you're not locking all users of doctrine into using one timestamp format.
        Hide
        Elizabeth M Smith added a comment - - edited
        $full = '2010-06-11 17:18:39.808397-04';
        $no_micro = '2010-06-11 17:18:39-04';
        $no_tz = '2010-06-11 17:18:39';
        var_dump(DateTime::createFromFormat('Y-m-d H:i:sO', $full));
        var_dump(DateTime::createFromFormat('Y-m-d H:i:s.uO', $full));
        
        var_dump(new DateTime($full));
        var_dump(new DateTime($no_micro));
        var_dump(new DateTime($no_tz));
        

        Quick test - why rely on createFromFormat when the datetime constructor is smart enough to handle all formats?

        Show
        Elizabeth M Smith added a comment - - edited $full = '2010-06-11 17:18:39.808397-04'; $no_micro = '2010-06-11 17:18:39-04'; $no_tz = '2010-06-11 17:18:39'; var_dump(DateTime::createFromFormat('Y-m-d H:i:sO', $full)); var_dump(DateTime::createFromFormat('Y-m-d H:i:s.uO', $full)); var_dump( new DateTime($full)); var_dump( new DateTime($no_micro)); var_dump( new DateTime($no_tz)); Quick test - why rely on createFromFormat when the datetime constructor is smart enough to handle all formats?
        Hide
        Benjamin Eberlei added a comment -

        One reason is probably performance, using the constructor takes double the time:

        $full = '2010-06-11 17:18:39.808397-04';
        $no_micro = '2010-06-11 17:18:39-04';
        $no_tz = '2010-06-11 17:18:39';
        
        $n = 5000;
        
        $ts = microtime(true);
        for ($i = 0; $i < $n; $i++) {
            DateTime::createFromFormat('Y-m-d H:i:sO', $no_micro);
            DateTime::createFromFormat('Y-m-d H:i:s.uO', $full);
        }
        echo "DateTime::createFromFormat: " . number_format(microtime(true) - $ts, 4)."\n";
        
        for ($i = 0; $i < $n; $i++) {
            new DateTime($full);
            new DateTime($no_micro);
        }
        echo "new DateTime: " . number_format(microtime(true) - $ts, 4)."\n";
        
        DateTime::createFromFormat: 0.1173
        new DateTime: 0.2496
        

        Since the DateFormat is pretty static, therefore using createFromFormat() seems an obvious choice. This is of course mainly a good reason for MySQL where you cannot change the format, and Postgres, Oracle and DB2 allow changes in precision and formatting of the date which make this decision a little bit more complicated.

        Show
        Benjamin Eberlei added a comment - One reason is probably performance, using the constructor takes double the time: $full = '2010-06-11 17:18:39.808397-04'; $no_micro = '2010-06-11 17:18:39-04'; $no_tz = '2010-06-11 17:18:39'; $n = 5000; $ts = microtime( true ); for ($i = 0; $i < $n; $i++) { DateTime::createFromFormat('Y-m-d H:i:sO', $no_micro); DateTime::createFromFormat('Y-m-d H:i:s.uO', $full); } echo "DateTime::createFromFormat: " . number_format(microtime( true ) - $ts, 4). "\n" ; for ($i = 0; $i < $n; $i++) { new DateTime($full); new DateTime($no_micro); } echo " new DateTime: " . number_format(microtime( true ) - $ts, 4). "\n" ; DateTime::createFromFormat: 0.1173 new DateTime: 0.2496 Since the DateFormat is pretty static, therefore using createFromFormat() seems an obvious choice. This is of course mainly a good reason for MySQL where you cannot change the format, and Postgres, Oracle and DB2 allow changes in precision and formatting of the date which make this decision a little bit more complicated.
        Hide
        Elizabeth M Smith added a comment -

        I think everyone BUT mysql allow changes in the precision and formatting of the date - heck SQL Server is the worst for "make the date be whatever you want"

        I think this would fall into "premature optimization" - yes it's a great speed improvement for mysql, but it's at the expense of all other DBs...

        Show
        Elizabeth M Smith added a comment - I think everyone BUT mysql allow changes in the precision and formatting of the date - heck SQL Server is the worst for "make the date be whatever you want" I think this would fall into "premature optimization" - yes it's a great speed improvement for mysql, but it's at the expense of all other DBs...
        Hide
        Benjamin Eberlei added a comment - - edited

        A Doctrine\DBAL\DBALException due to a wrong date in the database probably leads to a fatal error in any userland code, or would anybody catch all the errors? how would you handle them?

        We could think of changing the type to be without timezone, that would be a pretty massive BC though since it affects users database schemas.

        Another option would be a configurable format per Postgres/Oracle/DB2 Platform:

        $config = new \Doctrine\ORM\Configuration();
        $config->setDateTimeGlobalFormat('Y-m-d H:i:s.uO');
        

        However Type instances are flyweight instances, that means it would have to be the format of all Doctrine DateTime typed columns. However this way you would at least have full control over the format, not depending on any Doctrine 2 interpretation.

        You can of course add your own datetime type and overwrite the existing or use different ones. Maybe we should supply two DateTime types.

        Show
        Benjamin Eberlei added a comment - - edited A Doctrine\DBAL\DBALException due to a wrong date in the database probably leads to a fatal error in any userland code, or would anybody catch all the errors? how would you handle them? We could think of changing the type to be without timezone, that would be a pretty massive BC though since it affects users database schemas. Another option would be a configurable format per Postgres/Oracle/DB2 Platform: $config = new \Doctrine\ORM\Configuration(); $config->setDateTimeGlobalFormat('Y-m-d H:i:s.uO'); However Type instances are flyweight instances, that means it would have to be the format of all Doctrine DateTime typed columns. However this way you would at least have full control over the format, not depending on any Doctrine 2 interpretation. You can of course add your own datetime type and overwrite the existing or use different ones. Maybe we should supply two DateTime types.
        Hide
        Elizabeth M Smith added a comment - - edited

        I don't think an exception is a good idea
        A PHP warning/notice might be a better solution so you at least know what failed (it shouldn't kill the script though, as a fatal error and exception do)

        However a configurable would probably be a good idea...
        Or two datetime types - one that allows a format to be used
        Or even the ability to define a format for your datetime field (with a fallback to the default format)

        For databases that talk to other things, not just PHP via Doctrine, dictating the format of the datetime fields is not an option

        Show
        Elizabeth M Smith added a comment - - edited I don't think an exception is a good idea A PHP warning/notice might be a better solution so you at least know what failed (it shouldn't kill the script though, as a fatal error and exception do) However a configurable would probably be a good idea... Or two datetime types - one that allows a format to be used Or even the ability to define a format for your datetime field (with a fallback to the default format) For databases that talk to other things, not just PHP via Doctrine, dictating the format of the datetime fields is not an option
        Hide
        Benjamin Eberlei added a comment -

        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.

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

        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

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

        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

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

        Merged into Master now

        Show
        Benjamin Eberlei added a comment - Merged into Master now
        Hide
        Václav Novotný added a comment - - 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?

        Show
        Václav Novotný added a comment - - 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?

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Guilherme Blanco
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: