什么决定(如何配置)php PDO 驱动程序对日期和时间戳字段的值使用什么格式字符串? [英] What determines (how to configure) what format string is used by php PDO driver for values of date and timestamp fields?

问题描述

我有 Firebird 3.0 数据库，它有日期和时间戳字段，我在 PHP 7.x 中使用 interbase 扩展(是的 - 仍然是 interbase)和 Yii2 框架.我有 Yii 代码:

I have Firebird 3.0 database which have date and timestamp fields and I am using interbase extension (yes - still interbase) and Yii2 framwork in PHP 7.x. And I have Yii code:

Yii::$app->db->createCommand('select order_date from orders where order_id=5');

返回带有值的字段:

2019-10-15 00:00:00

AFAIU 然后 Yii2 使用 PDO，这就是为什么我的问题归结为关于 PDO 的问题.PHP 有 DateTime 扩展，但 AFAIU PDO 不使用 DateTime 类(也不使用 Unix 时间戳 - 秒整数)，而是根据某种格式返回字符串.

AFAIU then Yii2 uses PDO and that is why my question reduces to the question about PDO. PHP has DateTime extension but AFAIU PDO does not use DateTime class (and also does not use Unix timestamps - integer of seconds) and istead it just returns string according to some format.

我的问题是 - PDO 层对 SQL 日期和时间戳字段使用什么格式字符串，我该如何更改该格式字符串?我想确保返回的日期或时间戳采用指定格式，我可以从中创建 DateTime 实例并进一步操作此实例.例如.Firebird 3.0 还没有时区，这就是为什么我想调用 $dateTimeInstance->setTimezone(...) 来完全指定实例的时区部分，然后我可以安全地输出值在 const string DateTimeInterface::ISO8601 = "YmdTH:i:sO" 格式中，这是 JSON 普遍接受的日期时间格式>

My question is - what format string is used by PDO layer for SQL date and timestamp fields and how can I change that format string? I would like to be sure that the returned date or timestamp is in specified format from which I can created DateTime instance and manipulate this instance further. E.g. Firebird 3.0 has not timezones yet and that is why I would like to call $dateTimeInstance->setTimezone(...) to fully specify the timezone part of the instance and then I can safely output the value in const string DateTimeInterface::ISO8601 = "Y-m-dTH:i:sO" format that is commonly accepted datetime format for JSON>

推荐答案

如果您希望在 Yii2 中全局配置格式，您应该在您的通用配置中设置一些本地化设置:

If you are looking to confgure globally the format in Yii2, there are a few localized settings in your common config you should set:

配置示例

'timeZone' => 'America/New_York', // Default PHP date in Eastern.
//... other config stuff
'components' => [
    'formatter' => [
        'dateFormat' => 'php:Y-m-d',
        'datetimeFormat' => 'php:Y-m-d H:i:s',
        'decimalSeparator' => '.',
        'thousandSeparator' => ',',
        'defaultTimeZone' => 'UTC',       // DB Source is stored as UTC timezone.
        'timeZone' => 'America/New_York', // When formatter is used, convert to this timezone.
    ],
    //... other config stuff
  ]

时间检查

本地化时区管理起来很麻烦，因此请创建一个页面，以便您更好地了解本地化格式.请参阅 Yii2 国际化文档

Localized timezones can be a pain to manage, so create a page so you better understand localized formatting. See Yii2 Internationalization Docs

<?php
$now = (new yiidbQuery)->select('NOW()')->scalar(Yii::$app->db);
?>
<table class="table">
    <tr>
        <th>PHP UTC dateTime (+4 Hours):</th>
        <td><?= gmdate('Y-m-d H:i:s')?></td>
    </tr>
    <tr>
        <th>PHP local dateTime:</th>
        <td><?= date('Y-m-d H:i:s')?></td>
    </tr>
    <tr>
        <th>PHP local timeZone:</th>
        <td><?= date_default_timezone_get() ?></td>
    </tr>
    <tr>
        <th>Database Time Raw (expected UTC):</th>
        <td><?= $now ?></td>
    </tr>
    <tr>
        <th>Formatter -> UTC to local dateTime:</th>
        <td><?= Yii::$app->formatter->asDatetime(gmdate('Y-d-m H:i:s')) ?></td>
    </tr>
    <tr>
        <th>Formatter -> realtiveTime from UTC:</th>
        <td><?= Yii::$app->formatter->asRelativeTime(gmdate('Y-m-d H:i:s')) ?></td>
    </tr>
</table>

这篇关于什么决定(如何配置)php PDO 驱动程序对日期和时间戳字段的值使用什么格式字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！