PHP查询PostgreSQL的时间结果与DBeaver不一致问题求助

问题 #

我正在编写SQL查询从timestamptz字段提取日期和时间组件，SQL语句如下：

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 ;

在DBeaver中执行该查询结果正确，例如sl.changedon::time as changedon_time的结果为12:07:36，但使用PHP的pgsql扩展执行同一查询时，该字段结果为11:07:36.517733。

我的php.ini中已设置：

date.timezone = "Europe/Rome"

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中得到正确的时间？

原因与解决方案 #

差异原因 #

• DBeaver连接时会自动使用你设置的本地时区（Europe/Rome）对timestamptz类型数据做转换，因此提取的时间是转换后的本地时区时间。
• PHP的PDO连接PostgreSQL时，默认不会自动应用时区转换，timestamptz会被解析为UTC时间，所以提取的时间是UTC时区的结果（比Europe/Rome慢1-2小时，取决于夏令时），同时保留了毫秒精度。

解决方法 #

方法1：在PDO连接时指定时区

在DSN中添加时区参数，让PostgreSQL直接返回转换为Europe/Rome时区的时间值，修改后的连接代码如下：

$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'];
$dsn    .=";timezone=Europe/Rome"; // 新增时区配置

$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 );
}
catch (PDOException $e) {
    $_ENV['JIVENV']['DB'][$conn]['error'] = $e->getMessage();
}

方法2：修改SQL查询，强制指定时区转换

在提取时间组件时，明确指定转换为Europe/Rome时区，替换原SQL中的sl.changedon::time为如下内容：

sl.changedon AT TIME ZONE 'Europe/Rome'::time as changedon_time,

如果需要去掉毫秒精度，可以用TO_CHAR格式化时间：

TO_CHAR(sl.changedon AT TIME ZONE 'Europe/Rome', 'HH24:MI:SS') as changedon_time,

内容的提问来源于stack exchange，提问作者dorje