How Make Conversion From Utc Datetime Into My Timezone?

Hello, I’m trying to get “timezonned” datetime.

My posts table has create_at column and datetime type. It is means that model’s view output UTC datetime.




Дата создания: <?= $model->create_at ?>



Question: how can I make conversion from UTC datetime into my local timezone(ru_RU)?

I tried something like this, but still get nothing :(




Дата создания: <?= date('Y-m-d G:i:s', strtotime($model->create_at)) ?>



You need to append the string ’ UTC’ and try




$time = strtotime($model->create_at.' UTC');

$localTime = date("Y-m-d H:i:s", $time);



Note: This is only if you are sure that dates are stored as UTC format in db.

For MYSQL you can also try using the CONVERT_TZ function.

Thank you @Kartik V it works!

What type is better to store datetime into mysql db? I think, it is datetime, because it is editable unlike timestamp.

If so, what the best practice/way to render date and time within view files? Every time use something like this?




<?php

$create_at = strtotime($model->create_at.' UTC');

$update_at = strtotime($model->update_at.' UTC');

?>

Date Created: <?= date('Y-M-d G:i:s', $create_at) ?>

Date Updated: <?= date('Y-M-d G:i:s', $update_at) ?>



May be there is another solution?

Some deciding factors:

  • In MYSQL 5 and above, TIMESTAMP values are converted from the current time zone to UTC for storage, and converted back from UTC to the current time zone for retrieval. (This occurs only for the TIMESTAMP data type, and not for other types such as DATETIME.)

  • However, from useability perspective, DATETIME works in all varied scenarios and you can choose to use the native MYSQL functions like DATE_ADD(my_datetime, INTERVAL 1 DAY).

  • The TIMESTAMP on the contrary has a few restrictions:

[list]

  • The TIMESTAMP data type has a range of ‘1970-01-01 00:00:01’ UTC to ‘2038-01-09 03:14:07’ UTC. It has varying properties, depending on the MySQL version and the SQL mode the server is running in.

  • The supported range for DATETIME is ‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’.

  • You’re quite likely to hit the lower limit on TIMESTAMPs in general use – e.g. storing birth date.

[/list]

To make my code accessible across scenarios, I tend to use DATETIME in the DB for many cases - and do the formatting of the date via PHP for display.

You may want to create a global helper class for formatting dates before displaying it to the client. Something like:




class MyDate 

{

    public static function format($date) {

        $time = strtotime($date.' UTC');

        return date("Y-m-d H:i:s", $time);

    }

}



and use the global class function in your views:




    <?= MyDate::format($model->create_at) ?>



Thank you for intelligible answer! :rolleyes: Static method for formatting dates seems to be more beautiful solution)


Offtop:

Which the variant is more correct in terms of English usage: "…seems to be more beautiful solution" or "…seems more beautiful solution"?

I’ve found out this job could be made by Formatter base component.




'formatter' => [

    'class' => 'yii\i18n\Formatter', #php5-intl package is installed to work correctly

],






<?php

echo Yii::$app->formatter->timeZone; #Europe/Moscow

echo Yii::$app->formatter->asTime($model->update_at); #still UTC datetime <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/sad.gif' class='bbc_emoticon' alt=':(' />

?>



Update: Timezone works only when I append ’ UTC’ string at the end of parameter.

Question: Is Formatter class intended to use only with timestamps?

You can also refer my new extension yii2-datecontrol which handles this easily. Refer documentation and usage.