postgresql - Time values in php resultset are different from dbeaver client (postgres) - Stack Overflow

admin管理员组

文章数量:1187336

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();
    }

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();
    }

• php
• postgresql
• timezone

Share Improve this question Follow edited Jan 26 at 14:32 dorje asked Jan 26 at 12:32 dorjedorje 771010 bronze badges 21

• Depends on the timezone used in the output: "same output using php" – Honk der Hase Commented Jan 26 at 12:37
• So, based on your comment @HonkderHase, the output is not generated by the postgresql server? – dorje Commented Jan 26 at 13:07
• 1 If you make output like echo date('H:i:s', $dbValue) it will use PHP's default timezone... the database usually uses GMT, but also might have a different setting. You need to verify that both use the same timezone (or you must specify the timezone excplicitly, for both SQL query and PHP output) – Honk der Hase Commented Jan 26 at 13:18
• Check this out: onecompiler.com/postgresql/4377t645u – Olivier Commented Jan 26 at 13:31
• 2 As I showed in my first comment, you can execute SET TIME ZONE 'Europe/Rome'. – Olivier Commented Jan 26 at 14:19

 |  Show 16 more comments

1 Answer 1

Sorted by: Reset to default 2

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

本文标签： postgresqlTime values in php resultset are different from dbeaver client (postgres)Stack Overflow