Timezone

[daebak74]

Hi,
There is a way in $dataSource for set a timezone for example (SET time_zone = '+3:00'") ?

Thanks.

[ocram]

Can’t you just execute that statement via Database#exec?

Apart from that, one would usually set this globally in the configuration, I guess.

There’s nothing in this library specifically for setting the time zone (for a specific user or connection, or for the current session).

[daebak74]

Thanks for your replay. I wrote here but I Am using PHP Fundation. What is the right approach for set everything in unique timezone ignoring the server ? Even if I set in .env the timezone if I use in SELECT formar From timestamp return the timezone if the server.

Thank you very much

[ocram]

Setting the time zone in .env is the correct solution there. Your database, however, may return dates, times and timestamps in its own configured time zone, the connection time zone, or any time zone it wants.

So perhaps you may want to use $_ENV['APP_DEFAULT_TIMEZONE'] there and set the database connection to use that time zone. Or you set the database to UTC and convert everything to the correct time zone in PHP.

You might be interested in https://github.com/delight-im/PHP-Temporal for this purpose.

[daebak74]

I already was thinking about PHP Temporal you really make a great job. :) I'll include now. By the way I always need to connect the $_ENV['APP_DEFAULT_TIMEZONE'] with DB connection. I have date in timestamp and make this is fast thanks about your repository

$app->db()->selectRow("SELECT DATE_FORMAT(FROM_UNIXTIME(check_in), '%d/%m/%Y %H:%i') as check_in ...

But use the server timezone. There is a way for use in GLOBAL $_ENV['APP_DEFAULT_TIMEZONE'] with DB connection and don't need set for each call?

Any help are appreciate.
Thanks

[ocram]

By the way I always need to connect the $_ENV['APP_DEFAULT_TIMEZONE'] with DB connection.
…
There is a way for use in GLOBAL $_ENV['APP_DEFAULT_TIMEZONE'] with DB connection and don't need set for each call?

What are you doing exactly? Could you show how you set the time zone?

You should be able to configure a global time zone in your database configuration. If you don’t know how to do that, you may want to take a look at the documentation of your database software, as it is specific to that software.

Instead of DATE_FORMAT(FROM_UNIXTIME(check_in), '%d/%m/%Y %H:%i'), you can also just select check_in and format it in PHP, with the component mentioned above, and the time zone can be set automatically.

[daebak74]

Instead of DATE_FORMAT(FROM_UNIXTIME(check_in), '%d/%m/%Y %H:%i'), you can also just select check_in and format it in PHP, with the component mentioned above, and the time zone can be set automatically.

Yes I could but I should to make 2 step in this way I can get directly from $app->db()->selectRow(.. the formatted data and just because is an Ajax call is faster.

By the way I don't know if is the correct way but I solved adding at the the top after the PDO instance this line.

$app->db()->exec("SET time_zone='+2:00'");

Anyway could be helpfull to add in
....
$dataSource->setCharset('utf8mb4');
$dataSource->setUsername('my-username');
$dataSource->setPassword('my-password');

$dataSource->setTimeZone('00:00'); for UTC or any other for who wanna configure a default timezone.

:)

Thanks again for your professional support always fast and decisive.

[ocram]

Thanks, that call is exactly what I had in mind. Seems fine, if that’s how it works with your database, which, again, is specific to that database software and must be looked up in its documentation.

We will consider setting a time zone by default, or asking for a default value. But for now, because this can easily be added in your own application code, we won’t make any changes here as we are not really sure yet.