Users’ timezone issue onYii2 with PostgreSQL

I would like to ask for a better approach. I am using Yii2 with PostgreSQL, and set all timestamp fields for all table to timestamp with timezone data type.

timestamp with time zone NOT NULL DEFAULT now()

I have created setting which will be called for every response,

Yii::$app->setTimezone(MallSettings::getSetting('timezone', Yii::$app->session['mall_id']));

which will return something like Asia/Makassar or Asia/Jakarta (it depends on user’s setting).

I extend the yii18nFormatter, using a class named ZeedFormatter below, and make a new function to display the datetime value in user’s preferred timezone.

class ZeedFormatter extends yiii18nFormatter
{
    /**
     * Format timestamp with timezone value to user's preferred timezone.
     * (extends the asDatetime() functionality)
     * 
     * @param  [type] $value  [description]
     * @param  [type] $format [description]
     * @return [type]         [description]
     */
    public function asLocaldatetime($value, $format = null)
    {
        $originalDateTime = DateTime::createFromFormat('Y-m-d H:i:s.uO', $value);
        $originalDateTime->setTimezone(new DateTimeZone(date_default_timezone_get()));

        if ($format === null)
            $format = 'Y-m-d H:i:s.uO';

        $localDateTime = $originalDateTime->format($format);

        return $localDateTime;

    }
}

The idea is that whenever I need a ‘local’ datetime format, I can call it like so:

    Yii::$app->formatter->asLocaldatetime('2019-08-29 19:52:21.02886+07');
    // will return 2019-08-29 20:52:21.02886+08 for Asia/Makassar timezone

Or, if used inside a GridView,

       'position',
       'created_at:localdatetime',

       ['class' => 'backendwidgetsActionColumn'],

Can I get this functionality only using Yii2’s feature? Or (another option) is calling created_at AT TIME ZONE 'Asia/Makassar' from the Psql query? I hope I can get an enlightment. Thank you.

Answer

My (another) approach is to keep using the asDatetime() function.

I just realized that I need to remove / comment the timezone from config file. If the value is set there, no matter how I set the timezone at other place, Yii always gets the value from the config file (common/config/main.php) if we are using the asDatetime formatter.

    'components' => [
        'formatter' => [
            // 'class' => 'commonmodulesZeedFormatter',
            'locale' => 'id-ID',
            // 'timeZone' => 'Asia/Jakarta',
            'defaultTimeZone' => 'Asia/Jakarta',
            'dateFormat' => 'php:j M Y',
            'decimalSeparator' => ',',
            'thousandSeparator' => '.',
            'currencyCode' => 'Rp ',
            'nullDisplay' => '<em style="color:#d8d8d8">null</em>',
            'numberFormatterOptions' => [
                NumberFormatter::MIN_FRACTION_DIGITS => 0,
                NumberFormatter::MAX_FRACTION_DIGITS => 0,
            ],
        ],
    ]

If we need another (or customized) format, we can still use the ZeedFormatter as above and add some desired functions. For example :

    public function asMyformat($value)
    {
        $timestamp = strtotime($value);
        $fmt = new IntlDateFormatter('id-ID', IntlDateFormatter::NONE, IntlDateFormatter::LONG);

        $fmt->setPattern('hh:mm z');// will return something like '09:47 WITA' 

        return $fmt->format($timestamp);
    }