Framework: Eloquent doesn't set the timezone before querying using a date

[jamiehd]

• Laravel Version: 5.3.29
• PHP Version: 7.0
• Database Driver & Version: Mysql

Description:

When passing in a date to the query builder the timezone is not automatically converted to the application's timezone. This means that the queries are run for whatever the timezone of the carbon object is rather than whatever timezone your application requires.

Steps To Reproduce:

$from = Carbon::parse('2017-06-23 00:00:00', 'Europe/London');
$to = Carbon::parse('2017-06-23 01:00:00', 'Europe/London');

$model = new EloquentModelOfSomekind();
$model->whereBetween('created_at', [$from, $to']);

(Assuming the timezone is configured as UTC in the application)

We would expect the query to be

SELECT FROM table 
WHERE created_at BETWEEN 
'2017-06-23 01:00:00' AND '2017-06-23 02:00:00'

But instead it is

SELECT FROM table 
WHERE created_at BETWEEN 
'2017-06-23 00:00:00' AND '2017-06-23 01:00:00'

[themsaid]

Yes no automatic conversion happens, you have to deal with that yourself.

[jamiehd]

What is the point in having a timezone setting in the config if it isn't used when handling dates?

[themsaid]

Because Laravel builds a raw query based on the input you give, applying timezones or not is a decision made as per the use case requirements, that's why it's left for you to decide.

[ankurk91]

@jamiehd
You can use model Mutators

   // Task.php

    protected $dates = [
        'start_datetime',
        'created_at', 'updated_at', 'deleted_at'
    ];

    // in my app each user have own timezone settings
    public function setStartDatetimeAttribute($date)
    {
        $this->attributes['start_datetime'] = \Carbon::parse($date, $this->user->timezone)->tz(config('app.timezone'));
    }

This way task start_datetime will be stored in app timezone (UTC in my case)
then
when you need to show this date to user on view, you have convert it into user's timezone.

$task->start_datetime->tz($user->timezone)->format('j M Y, g:i a')

Here are carbon docs
http://carbon.nesbot.com/docs/

[jamiehd]

Thanks for that @ankurk91, however I am trying to do this for select queries based on date range rather than when displaying the dates.

I found the following in prepareBindings in Illuminate\Database\Connection:

if ($value instanceof DateTimeInterface) {
    $bindings[$key] = $value->format($grammar->getDateFormat());

but I can't work out a way to override this method to perform the conversion. Is there an easy way to override this method so I can add my own timezone conversion into it?

Thanks

[delmicio]

hey @jamiehd have you found a way to override the method? I've encounter with this issue as well :/

[t202wes]

@jamiehd @delmicio - I've been having this issue for a while, and there doesn't appear to be any good way to do this yet. Any idea?

[delmicio]

@t202wes I just went by changing timezone when needed :(

 \Carbon::parse('2017-01-01 14:30', 'America/New_York')
    ->tz(config('app.timezone')); // reset to app timezone UTC before using the date on Eloquent

// 2017-01-01 19:30:00.0 UTC

[mfn]

I used the following trait to override asDateTime in the models were I need that; YMMV:

/**
 * Always treats dates as DateTime objects and adjusts their timezone
 * to app.timezone, if different.
 *
 * In combination with Eloquent\Model protected $dates, this trait can ensure
 * that:
 *   a) whever date (string, \DateTime, \Carbon\Carbon) will always
 *      be treated as \DateTime
 *   b) If the timezone of that \DateTime differs from app.timezone, it will
 *      be converted to it
 *
 * This is useful if have app.timezone *and* want all dates in the database
 * to be in app.timezone too.
 *
 * Requirement:
 * - the date attributes name must be added to the models protected
 *   $dates white-list
 *
 * Note: if you pass a \DateTime and changing the timezone is necessary, a
 * clone will be created so your original \DateTime is *not* mutated.
 */
trait ConvertAlwaysDateTimeToDefaultTimezoneTrait
{
    /**
     * Return a timestamp as DateTime object.
     *
     * @param  mixed $value
     * @return Carbon
     */
    protected function asDateTime($value): Carbon
    {
        // If this value is an integer, we will assume it is a UNIX timestamp's value
        // and delegate it immediately to the parent
        if (is_numeric($value)) {
            return parent::asDateTime($value);
        }

        // Flag to prevent creating a \DateTime from a string and then later
        // cloning it unnecessarily
        $justCreated = false;

        if (!($value instanceof \DateTime)) {
            $value = new \DateTime($value);
            $justCreated = true;
        }

        if ($value->getTimezone()->getName() !== config('app.timezone')) {
            if (!$justCreated) {
                $value = clone $value;
            }
            $value->setTimezone(new \DateTimeZone(config('app.timezone')));
        }

        return parent::asDateTime($value);
    }
}

[amitshahc]

Most irresponsible answers I noticed from Laravel. People around the world have enough test cases for dynamic timezone conversion feature based on the end user location.

For me, i am struggling with converting timezone while setting and getting it from database. For example, My project have shops registered worldwide. but i want to save the appointment times in UTC. so I need to handle the incoming requests first in UTC and save it in db. then before retrieve them for select query time range, then to display it to end user. everything need to manage precisely and carefully. This must be very basic feature in any framework.

[jamiehd]

For anyone else who comes across this - I now use some macros to make it easier to convert to and from the local timezone to utc in my controllers:

/**
 * @return Carbon
 */
public function toLocalTimezone()
{
    return function () {
        $new = $this->copy();
        $new->setTimezone(config('app.display_timezone'));
        return $new;
    };
}
/**
 * @param string $time
 * @return Carbon
 */
public static function fromLocalTimezone()
{
    return function ($localTime) {
        $date = Carbon::parse($localTime, config('app.display_timezone'));
        $date->setTimezone(config('app.timezone'));
        return $date;
    };
}

You just add them into a class, then use Carbon::mixin to bind them.

In my controllers I can then call Carbon::fromLocalTimezone($request->date) when accepting input, and $date->toLocalTimezone() when passing the responses back out.