What determines (how to configure) what format string is used by php PDO driver for values of date and timestamp fields?

Question

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');

which returns field with value:

2019-10-15 00:00:00

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.

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>

Answer 1

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

Config Example

'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
  ]

Time Checks

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>