是什么决定(如何配置)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中使用库间扩展(是-仍是库间)和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 = "Y-m-d\TH:i:sO"格式输出值，该值是JSON>普遍接受的datetime格式./p>

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-d\TH: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 \yii\db\Query)->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屋！