Doctrine DBAL
  1. Doctrine DBAL
  2. DBAL-860

MsSQL-Server DateTime microseconds issue

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 2.0.1
    • Fix Version/s: 2.2
    • Component/s: Platforms
    • Security Level: All
    • Labels:
      None
    • Environment:
      WindowsXP/Windows2008 R2 / PHP 5.3 / MsSQL-Server 2005 / MsSQL-PDO_PHP-Driver

      Description

      The string for the function getDateTimeFormatString() in the MsSqlPlatform class is 'wrong'.
      The Microsoft-SQL-Server just uses 3 digits for microseconds and not 6.
      So the string 'Y-m-d H:i:s.u' fails and the server states: [SQL Server]Fehler beim Konvertieren einer Zeichenfolge in einen datetime-Wert (Error when converting a string to a datetime-value) .

      So this string works, but does not regard the microseconds for those how rely on them: 'Y-m-d H:i:s.000'

      See also:
      [...] The MS datetime column is documented to have an accuracy of only about .3 seconds anyway [...]
      http://bytes.com/topic/sql-server/answers/80150-inserting-datetime-milliseconds-sql-server

      http://msdn.microsoft.com/en-gb/library/ms186819.aspx (Section: Remarks)

        Activity

        Martin Weise created issue -
        Hide
        Benjamin Eberlei added a comment -

        Assigned to juozas, but i think the issue here is that you have to use Datetime2, or add your own type replacing the shipped one to support the old datetime.

        Show
        Benjamin Eberlei added a comment - Assigned to juozas, but i think the issue here is that you have to use Datetime2, or add your own type replacing the shipped one to support the old datetime.
        Benjamin Eberlei made changes -
        Field Original Value New Value
        Assignee Benjamin Eberlei [ beberlei ] Juozas Kaziukenas [ juokaz ]
        Hide
        Martin Weise added a comment -

        Ok, I had another problem with the datetime, but this does not regard the problem of this issue ( at least not totally).
        The problem with the MsSQL-Server before 2008 is that there is no data type named 'datetime2', just 'datetime'.
        The next problem is that every date conversion for a query is done in the language set upon conection time.
        Thus leads to a problem, when it is not possible to set the connection language.

        So the problem is that the MsSQL-Server relies on the settings above.
        In my case the datetime conversion failed, as the server always thought that the datetime-string would come in
        the following format: Y-d-m . This is not true, as the default format string is: Y-m-d . So every insert/update query fails.
        To solve the problem I did that: $entityManager->getConnection()->exec('SET DATEFORMAT ymd'); .
        This way I ensured that the dateformat string works fine, except the issue problem.

        To solve the problem in general, it would be helpful to subclass the MsSqlPlatform into a class named MsSql2005Platform or something like this and just override the getDateTimeFormatString and upon connection setting the format for the queries
        as mentioned before.

        Hope this helps out.
        Besides, here a link to the datetime problem (in german): http://www.insidesql.org/blogs/frankkalis/2010/08/19/der-ultimative-guide-fuer-die-datetime-datentypen

        Show
        Martin Weise added a comment - Ok, I had another problem with the datetime, but this does not regard the problem of this issue ( at least not totally). The problem with the MsSQL-Server before 2008 is that there is no data type named 'datetime2', just 'datetime'. The next problem is that every date conversion for a query is done in the language set upon conection time. Thus leads to a problem, when it is not possible to set the connection language. So the problem is that the MsSQL-Server relies on the settings above. In my case the datetime conversion failed, as the server always thought that the datetime-string would come in the following format: Y-d-m . This is not true, as the default format string is: Y-m-d . So every insert/update query fails. To solve the problem I did that: $entityManager->getConnection()->exec('SET DATEFORMAT ymd'); . This way I ensured that the dateformat string works fine, except the issue problem. To solve the problem in general, it would be helpful to subclass the MsSqlPlatform into a class named MsSql2005Platform or something like this and just override the getDateTimeFormatString and upon connection setting the format for the queries as mentioned before. Hope this helps out. Besides, here a link to the datetime problem (in german): http://www.insidesql.org/blogs/frankkalis/2010/08/19/der-ultimative-guide-fuer-die-datetime-datentypen
        Hide
        Juozas Kaziukenas added a comment -

        I have somehow manage to miss the fact that datetime2 wasn't around in datetime... What I'm thinking now is there a need for datetime2 in Doctrine at all. If only thing it brings is additional accuracy for microseconds, maybe best idea would be to use datetime for 2008 installs too if used from Doctrine. However datetime is now a standard and Microsoft recommends to use it for new installs. What I can do is I can always insert 3 fractional points to datetime column as both datetime2 and datetime would accept it as valid date string.

        We can have separate platforms for 2008 and 2005 servers, but that would be quite resource intensive. Let me see what is the best way to fix it.

        Regards to Dateformat, I guess the solution would be to set format on connection, how you suggested. How about you create a separate ticket for this and assign it to me.

        Show
        Juozas Kaziukenas added a comment - I have somehow manage to miss the fact that datetime2 wasn't around in datetime... What I'm thinking now is there a need for datetime2 in Doctrine at all. If only thing it brings is additional accuracy for microseconds, maybe best idea would be to use datetime for 2008 installs too if used from Doctrine. However datetime is now a standard and Microsoft recommends to use it for new installs. What I can do is I can always insert 3 fractional points to datetime column as both datetime2 and datetime would accept it as valid date string. We can have separate platforms for 2008 and 2005 servers, but that would be quite resource intensive. Let me see what is the best way to fix it. Regards to Dateformat, I guess the solution would be to set format on connection, how you suggested. How about you create a separate ticket for this and assign it to me.
        Hide
        Benjamin Eberlei added a comment -

        Oracle also has an Session Init Listener that handles the date format things, i guess we can take this as example. However I think having Mssql2005Platform sounds goods also, it would be only one method to override.

        Show
        Benjamin Eberlei added a comment - Oracle also has an Session Init Listener that handles the date format things, i guess we can take this as example. However I think having Mssql2005Platform sounds goods also, it would be only one method to override.
        Hide
        Martin Weise added a comment -

        To solve this issue, at least for MsSQL-Server datetime data types, change the following TypeClass of Doctrine by adding
        this check before converting to PHP\DateTime in 'convertToPHPValue()' :

        if( strlen($value) == 24 && $platform->getDateTimeFormatString() == 'Y-m-d H:i:s.u')
        $value = $value.'000';

        I know this is propably very specific, but I do not know, how other DBs handle microseconds in datetime strings.

        Show
        Martin Weise added a comment - To solve this issue, at least for MsSQL-Server datetime data types, change the following TypeClass of Doctrine by adding this check before converting to PHP\DateTime in 'convertToPHPValue()' : if( strlen($value) == 24 && $platform->getDateTimeFormatString() == 'Y-m-d H:i:s.u') $value = $value.'000'; I know this is propably very specific, but I do not know, how other DBs handle microseconds in datetime strings.
        Hide
        Martin Weise added a comment - - edited

        I fixed this bug with some changes in the DateTimeType class. As there is no Explicit MSSQL2005 Plattform this change would also affect datetime2 type in the SQLServer 2008 plattform, which is the data type that has 6 microseconds.
        So either populate a MSSQLServer2005 Plattform, or introduce a new DateTimeType for the 2005 platform.

        DateTimeType.php
        
        <?php
        /*
         * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
         * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT
         * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR
         * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT
         * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL,
         * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT
         * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
         * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY
         * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
         * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE
         * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
         *
         * This software consists of voluntary contributions made by many individuals
         * and is licensed under the LGPL. For more information, see
         * <http://www.doctrine-project.org>.
         */
        
        namespace Doctrine\DBAL\Types;
        
        use Doctrine\DBAL\Platforms\AbstractPlatform;
        
        /**
         * Type that maps an SQL DATETIME/TIMESTAMP to a PHP DateTime object.
         *
         * @since 2.0
         */
        class DateTimeType extends Type
        {
            public function getName()
            {
                return Type::DATETIME;
            }
        
            public function getSQLDeclaration(array $fieldDeclaration, AbstractPlatform $platform)
            {
                return $platform->getDateTimeTypeDeclarationSQL($fieldDeclaration);
            }
        
            public function convertToDatabaseValue($value, AbstractPlatform $platform)
            {
        		if( $value === null)
        			return null;
        
        		$value = $value->format($platform->getDateTimeFormatString());
        
        		if( strlen($value) == 26 &&
        			$platform->getDateTimeFormatString() == 'Y-m-d H:i:s.u' &&
        			$platform instanceof \Doctrine\DBAL\Platforms\MsSqlPlatform )
        			$value = substr($value, 0, \strlen($value)-3);
        
        		return $value;
        
            }
        
            public function convertToPHPValue($value, AbstractPlatform $platform)
            {
                if ($value === null) {
                    return null;
                }
        
        		if( strlen($value) == 24 && $platform->getDateTimeFormatString() == 'Y-m-d H:i:s.u')
        			$value = $value.'000';
        
                $val = \DateTime::createFromFormat($platform->getDateTimeFormatString(),$value);
                if (!$val) {
                    throw ConversionException::conversionFailedFormat($value, $this->getName(), $platform->getDateTimeFormatString());
                }
                return $val;
            }
        }
        
        
        Show
        Martin Weise added a comment - - edited I fixed this bug with some changes in the DateTimeType class. As there is no Explicit MSSQL2005 Plattform this change would also affect datetime2 type in the SQLServer 2008 plattform, which is the data type that has 6 microseconds. So either populate a MSSQLServer2005 Plattform, or introduce a new DateTimeType for the 2005 platform. DateTimeType.php <?php /* * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. * * This software consists of voluntary contributions made by many individuals * and is licensed under the LGPL. For more information, see * <http: //www.doctrine-project.org>. */ namespace Doctrine\DBAL\Types; use Doctrine\DBAL\Platforms\AbstractPlatform; /** * Type that maps an SQL DATETIME/TIMESTAMP to a PHP DateTime object. * * @since 2.0 */ class DateTimeType extends Type { public function getName() { return Type::DATETIME; } public function getSQLDeclaration(array $fieldDeclaration, AbstractPlatform $platform) { return $platform->getDateTimeTypeDeclarationSQL($fieldDeclaration); } public function convertToDatabaseValue($value, AbstractPlatform $platform) { if ( $value === null ) return null ; $value = $value->format($platform->getDateTimeFormatString()); if ( strlen($value) == 26 && $platform->getDateTimeFormatString() == 'Y-m-d H:i:s.u' && $platform instanceof \Doctrine\DBAL\Platforms\MsSqlPlatform ) $value = substr($value, 0, \strlen($value)-3); return $value; } public function convertToPHPValue($value, AbstractPlatform $platform) { if ($value === null ) { return null ; } if ( strlen($value) == 24 && $platform->getDateTimeFormatString() == 'Y-m-d H:i:s.u') $value = $value.'000'; $val = \DateTime::createFromFormat($platform->getDateTimeFormatString(),$value); if (!$val) { throw ConversionException::conversionFailedFormat($value, $ this ->getName(), $platform->getDateTimeFormatString()); } return $val; } }
        Hide
        Benjamin Eberlei added a comment -

        Added SQLServer2005 platform that uses DATETIME and the .000 format as per instructions of Martin.

        Show
        Benjamin Eberlei added a comment - Added SQLServer2005 platform that uses DATETIME and the .000 format as per instructions of Martin.
        Benjamin Eberlei made changes -
        Status Open [ 1 ] Resolved [ 5 ]
        Fix Version/s 2.2 [ 10157 ]
        Resolution Fixed [ 1 ]
        Benjamin Eberlei made changes -
        Workflow jira [ 12379 ] jira-feedback [ 14761 ]
        Benjamin Eberlei made changes -
        Workflow jira-feedback [ 14761 ] jira-feedback2 [ 16625 ]
        Benjamin Eberlei made changes -
        Workflow jira-feedback2 [ 16625 ] jira-feedback3 [ 18878 ]
        Steve Müller made changes -
        Project Doctrine 2 - ORM [ 10032 ] Doctrine DBAL [ 10040 ]
        Key DDC-1028 DBAL-860
        Affects Version/s 2.0.1 [ 10115 ]
        Affects Version/s 2.0.1 [ 10114 ]
        Fix Version/s 2.2 [ 10142 ]
        Fix Version/s 2.2 [ 10157 ]
        Component/s Platforms [ 10056 ]
        Component/s ORM [ 10012 ]

        This list may be incomplete, as errors occurred whilst retrieving source from linked applications:

        • Request to http://www.doctrine-project.org/fisheye/ failed: Error in remote call to 'FishEye 0 (http://www.doctrine-project.org/fisheye/)' (http://www.doctrine-project.org/fisheye) [AbstractRestCommand{path='/rest-service-fe/search-v1/crossRepositoryQuery', params={query=DBAL-860, expand=changesets[0:20].revisions[0:29],reviews}, methodType=GET}] : Received status code 503 (Service Temporarily Unavailable)

          People

          • Assignee:
            Juozas Kaziukenas
            Reporter:
            Martin Weise
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved: