Add an option to enable "auto timezone conversion" for MySQL queries.

[Tankonyako]

Carbon Date Parsing and Timezone Issues

Carbon boasts the capability to parse dates, including those with specified timezones like "2023-12-17T09:48:30.416754+00:00." It accurately interprets dates from user-provided inputs along with their specified timezones.

Timezone Inheritance in Functions

However, challenges arise when utilizing certain functions, such as now(), as they inherit the timezone from the app.php configuration. This can lead to unexpected results in date comparisons.

Example Scenario

Consider a situation where the server operates in a +2 timezone. If a query compares the created_at field to an input_date of "2023-12-17T09:48:30.416754+00:00," the comparison may yield unexpected results. Even if a record was created at "2023-12-17 11:40:00" (created_at), it might satisfy the condition (created_at <= input_date) because on SQL condition builder, there is no conversion of timezone to the server timezone.

I was write solution here: #19737 (comment)

[kylekatarnls]

Carbon boasts the capability to parse dates, including those with specified timezones like "2023-12-17T09:48:30.416754+00:00."

Carbon maintainer here, I wouldn't say Carbon boasts such thing :D DateTime parses those kind of string and properly find the timezone natively. It's all from PHP itself.

"auto timezone conversion"

This sounds like you're expecting something to magically guess what timezone you want.

Consider a situation where the server operates in a +2 timezone

False problem here. Why would you even care to know where is your server and what is its timezone. When developing app, set everything to UTC and assume the application can be deployed anywhere on any server.

Same for your database, it could be hosted on a server with a different timezone. So just set your DB timezone to UTC too. BTW you should not even care your DB timezone, as you likely don't want to use any SQL function such as TODAY() or NOW(), it's way better to handle this in your application layer.

Timezone is not a thing for the server, it's a client thing, if you're doing stuff for a user, then what you care about is the user context: the timezone on the device the user is currently using (or used last time), if you're doing stuff for a place/event/trip, you care about the timezone there, if you're not in any user or geographical context, you shouldn't work with an arbitrary "server" timezone but with UTC.

I go more into details in this article:

https://kylekatarnls.medium.com/always-use-utc-dates-and-times-8a8200ca3164

[Tankonyako]

no, laravel not converting timezones on where (when doing eloquent). if i pass date with timezone, it looking in UTC but date in another timezone.

[kylekatarnls]

Sorry, "no" to what? Because my point is that you should not have a date in another timezone nor pass date with timezone but store all your date as UTC. Timezone is to be used when presenting to the user when you know they use a device with a particular timezone, because a user in Tokyo does not care your server is in Berlin or New York, they want to see date/time for Tokyo timezone, then storing the date as UTC is way better than an arbitrary city.

[Tankonyako]

problem in another. i store dates in UTC. but when i receive date from clients using date method of request, it giving me Carbon instance with timezone. When i passing this to where method, no conversation to UTC happen from another timezone, and it ignore timezone

[kylekatarnls]

OK, I see the point, it makes some sense, but changing something there might be a big surprise for users already relying the timezone to be truncated.

Also, this idea is not tied to Carbon, and does not need to be Carbon specific, any DateTimeInterface object has a timezone that could be taken into account or not when the query builder is taking it and converting it into a datetime (note if column is timestamp and the value is converted to a timestamp, there is no timezone truncation happenning). So I wonder why in your solution you use if ($value instanceof Carbon) and not just apply the hack to any DateTimeInterface?

Also, a query (from MySQL and many engines) does not contain a timezone info linked to the date so it will have to rely on the configuration and to assume the data store is actually of this timezone (which should be the case, but in practice it can be tweaked in various ways).

Last point from your solution I would do differently is the user date_default_timezone_get() assuming the php.ini has a proper timezone matching what is/should be stored in DB will be wrong in many cases. I think date_default_timezone_get() should just not be used and config taken from the Laravel config, not from the PHP so you can move/upgrade app with less dependency to the underlying software configs.

So:

if ($value instanceof DateTimeInterface)
{
    $bindings[$key] = $value->setTimezone(new DateTimeZone(config('mysql.timezone')))->format($grammar->getDateFormat());
}

Just as an example mysql should be dynamically replaced by the current SQL storage engine, and the DateTimeZone could be cached not to be re-created for every binding.