Time values in php resultset are different from dbeaver client (postgres)

I'm working on a query that extracts also date and time components from a timestamptz field.

select
    sl.id,
    sl.statusfrom,
    sl.statusto,
    sl.changedby,
    sl.changedon,
    sl.changedon::date as changedon_date,
    sl.changedon::time as changedon_time,
    TO_CHAR (sl.changedon, 'DD/MM/YYYY') as changedon_date_formatted,
    sl.beforerecord,
    ss.description as status_description,
    cu.username
from
    core_sm_log sl
    left join core_sm_states ss on ss.object_name = sl.objecttype and ss.state_code = sl.statusto
    left join core_users cu on cu.id = sl.changedby
where
    sl.objectId =:objectId
    and sl.objectType =:objectType
order by
    sl.changedon desc ;

Basically, executing the query in dbeaver, results are correct. For example, the sl.changedon::time as changedon_time gives value 12:07:36. The same result using php with pgsql extension gives value 11:07:36.517733. I wasn't able to find out some help on this... Anyone knows why is the result different for the same data and what configuration should I change in order to have correct time in php also?

I've this line in php.ini:

date.timezone = "Europe/Rome"

And this is my connection code part in php:

    $dsn    = $_ENV['JIVENV']['DBCONF'][$conn]['DB_ENGINE'];
    $dsn    .=":host=".     $_ENV['JIVENV']['DBCONF'][$conn]['DB_HOST'];
    $dsn    .=";port=".     $_ENV['JIVENV']['DBCONF'][$conn]['DB_PORT'];
    $dsn    .=";dbname=".   $_ENV['JIVENV']['DBCONF'][$conn]['DB_NAME'];

    $user   = $_ENV['JIVENV']['DBCONF'][$conn]['DB_USER'];
    $pass   = $_ENV['JIVENV']['DBCONF'][$conn]['DB_PASS'];

    try {
        $_ENV['JIVENV']['DB'][$conn] = new PDO($dsn, $user, $pass);
        $_ENV['JIVENV']['DB'][$conn]->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );    // set up PDO in exception mode
    }
    catch (PDOException $e) {
        $_ENV['JIVENV']['DB'][$conn]['error'] = $e->getMessage();
    }

Solution

The date.timezone php.ini you've chosen is for the PHP DateTime extension (php.net).

For the database connection you use the PHP Database Objects (PDO) extension (php.net) with the PostgreSQL PDO Driver (PDO_PGSQL) (php.net).

This just upfront to explain why that setting alone does not influence your PDO PostgreSQL connection directly: The setting that affects the string formatting/display/representation of date time database values (remote data) is by the server (postgres), not the client (php) and needs to be configured for the session (the database connection you open from php).

The PostgreSQL setting is TimeZone (string) (postgresql.org):

Sets the time zone for displaying and interpreting time stamps. The built-in default is GMT, but that is typically overridden in postgresql.conf; initdb will install a setting there corresponding to its system environment. See Section 8.5.3 for more information.

Now, let's bind that date.timezone setting to the database connection:

// php.ini: date.timezone = Europe/Rome

// PostgreSQL PDO DSN (without options)
$dsn = 'pgsql:host=localhost;user=postgres;password=set';

// add the timezone option
$dsn .= ';options=-ctimezone='.ini_get('date.timezone');

DSN: Data Source Name
PDO: PHP Database Objects

Full example:

When we use a standard PostgreSQL server and connect to it via PDO, we can show the default timezone in use.

It is "Etc/UTC" here (on a fresh DB instance), a simplified PHP code example that shows the Data Source Name (DSN) exactly in use like in your questions example and uses PDO::query() (php.net) under the hood:

// PDO Flyweight (DSN based) query helper
$fetch = fn (string $dsn) => fn ($query) => (new pdos)($dsn, $query);

// PostgreSQL PDO DSN (without options)
$dsn = 'pgsql:host=localhost;user=postgres;password=set';

$pgsql1 = $fetch($dsn);

echo $pgsql1('SHOW TIME ZONE'), PHP_EOL; // Etc/UTC

We can now provide postgresql connection options via the PDO DSN and set the timezone to our liking:

// PostgreSQL PDO DSN with timezone option
// php.ini: date.timezone = Europe/Rome
$pgsql2 = $fetch($dsn.';options=-ctimezone='.ini_get('date.timezone'));
                        #############################################

echo $pgsql2('SHOW TIME ZONE'), PHP_EOL; // Europe/Rome

Compare with postgres default timezone (Q&A), it shows the use of both SHOW and SET for TIME ZONE and explains that it is a session parameter. From 8.5.3. Time Zones (postgresql.org):

The SQL command SET TIME ZONE sets the time zone for the session. This is an alternative spelling of SET TIMEZONE TO with a more SQL-spec-compatible syntax.

Therefore, alternatively to using the DSN, we can also execute the statement on the PDO object, for example when it is created:

// php.ini: date.timezone = Europe/Rome

class PDOTimeZoned extends PDO 
{
    public function __construct($dsn, $user = null, $pass = null, $options = []) 
    {
        parent::__construct($dsn, $user, $pass, $options);
        $this->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $this->exec(sprintf("SET TIME ZONE '%s'", ini_get('date.timezone')));
    }
}

$pdo = new PDOTimeZoned($dsn);

echo $pdo->query('SHOW TIME ZONE')->fetchColumn(), PHP_EOL; // Europe/Rome