Timestamp type conversion with PostgreSQL

dohnal

Hello,

I have upgraded to Nette 2.4 from 2.3 and I am experiencing a trouble with parsing DateTime objecs from SQL query result.

For the PostgreSQL column type “timestamp without time zone” or “date”, I get the correct object, which gets formatted in ISO8601 as follows:
last_modif | timestamp without time zone << PostgreSQL data type
“last_modif”: “2017–01–28T12:24:02+01:00” << Nette formatted value

production_year | date << PostgreSQL data type
“production_year”: “2006–01–01T00:00:00+01:00”, << Nette formatted value

If I have a column of “timestamp with time zone”, it fails to convert it from string value:
last_modif | timestamp with time zone << PostgreSQL data type
“last_modif”: “2017–01–28 19:33:02.829367+01”, << Nette formatted value (original value returned by PostgreSQL)

Can you please help me how to fix it without doing “new \DateTime($row[‘last_modif’]);” on every column of “timestamp with time zone” data type?

Thanks a lot,

Vlasta

dohnal

The issue is in nette/Database/Helper.php where the data type is detected and converted to a proper internal type. It was changed in 2.4 in a way that function detectType matched the whole data type name, so the suffix " WITH TIME ZONE" was not matched.

The array typePatterns should like this (see “STAMP.*” where “.*” was added):

<?php
        public static $typePatterns = [
                '^_' => IStructure::FIELD_TEXT, // PostgreSQL arrays
                '(TINY|SMALL|SHORT|MEDIUM|BIG|LONG)(INT)?|INT(EGER|\d+| IDENTITY)?|(SMALL|BIG|)SERIAL\d*|COUNTER|YEAR|BYTE|LONGLONG|UNSIGNED BIG INT' => IStructure::FIELD_INTEGER,
                '(NEW)?DEC(IMAL)?(\(.*)?|NUMERIC|REAL|DOUBLE( PRECISION)?|FLOAT\d*|(SMALL)?MONEY|CURRENCY|NUMBER' => IStructure::FIELD_FLOAT,
                'BOOL(EAN)?' => IStructure::FIELD_BOOL,
                'TIME' => IStructure::FIELD_TIME,
                'DATE' => IStructure::FIELD_DATE,
                '(SMALL)?DATETIME(OFFSET)?\d*|TIME(STAMP.*)?' => IStructure::FIELD_DATETIME,
                'BYTEA|(TINY|MEDIUM|LONG|)BLOB|(LONG )?(VAR)?BINARY|IMAGE' => IStructure::FIELD_BINARY,
        ];
?>