Framework: [Bug][Feature] Convert datetime to UTC before saving into database.

[airlanggacahya]

Cross timezone aware web application should save all DateTime field in UTC format and convert it back into application timezone settings when retrieving it back.

I use this BaseModel patch to convert all DateTime field into UTC timezone and convert it back. BaseModel extends Eloquent.

// Patch for saving all date time into UTC timezone
protected function fromDateTime($value)
{
    if ($value instanceof DateTime && $value->getTimezone()->getName() != 'UTC') {
        $value->setTimezone(new DateTimeZone('UTC'));
    }

    return parent::fromDateTime($value);
}

// Patch to convert back time from UTC format into Application wide timezone
protected function asDateTime($value)
{
    $date = parent::asDateTime($value);
    $date = new Carbon($date->toDateTimeString(), 'UTC');
    $date->timezone = Config::get('app.timezone');
    return $date;
}

Benefit from saving in UTC is when Application wide timezone is changed, all DateTime field in database is still pointing into the right date and time.

[jasonlewis]

Isn't this what date_default_timezone_set is for?

[airlanggacahya]

No, that not what I mean.
Timezone is hard problem and honestly I don't know how to tackle it in
Laravel yet.
References why I propose to save datetime as UTC in database regardless of
app/config/app.php timezone settings:
http://stackoverflow.com/questions/6841333/why-is-subtracting-these-two-times-in-1927-giving-a-strange-result

2013/7/10 Jason Lewis [email protected]

Isn't this what date_default_timezone_sethttp://php.net/date_default_timezone_setis for?

—
Reply to this email directly or view it on GitHubhttps://github.com/laravel/framework/issues/1841#issuecomment-20724808
.

[crynobone]

This wouldn't be a problem as long as you maintain app.timezone configuration to "UTC". If you need to customise it per user it would be a different use case and should be handle by the app.

[JoostK]

I also store datetimes as UTC (like you should) and have a custom DateFormatter class that converts to the user's timezone and then displays it.

I think in your backend, you always want to use UTC datetimes (so not only in the database) and only when it is to be displayed, convert it. The same is true for dates entered in a form, convert them to UTC first and only then use them.

[dietervds]

Storing any datetime as UTC seems to be a generally accepted 'best practice', only converting them to the desired timezone at the very last moment.
I'm all for it to do the database UTC storage automatically.

[Brandon-Godwin]

:+1:

Configuring app.timezone to UTC works fine when you're dealing with dates that don't include a timezone. But if you're creating DateTime objects from input that specifies a timezone, it would be great to have Eloquent automatically convert these to UTC before inserting them into the database.

[taylorotwell]

I don't think we should magically convert times to a given timezone.

[jeremyfrench]

I think perhaps the docs could be clearer on this.

By setting app timezone as UTC I assumed that whatever timezone my dates were in that they would be saved as UTC. Where as they are saved just as a time and then when they are loaded it is assumed they are UTC.

[mattstauffer]

@jeremyfrench Consider writing a pull request to the docs to explain this further:

https://github.com/laravel/docs

[oleynikd]

This is still a big mess:

'timezone' => 'UTC', in my config/app.php
I have these 2 dates:

Carbon\Carbon {#598
     +"date": "2016-03-26 20:22:11.000000",
     +"timezone_type": 2,
     +"timezone": "EET",
}

and

Carbon\Carbon {#600
     +"date": "2016-03-26 20:22:11.000000",
     +"timezone_type": 3,
     +"timezone": "UTC",
}

When I save both of them to DB with Eloquent I get 2016-03-26 20:22:11 for both of them!
IMHO this is very unexpected, isn't it?

[andybeesee]

I think this can be done without it seeming magical. If given a Carbon or DateTime instance with a timezone that doesn't match the default then convert it to the default. I can't imagine many people are explicitly setting the timezone on a per model instance without a reason.

Like the following case where UTC is the app default, but a different timezone is explicitly set..

$timestamp = new Carbon('2016-01-01 00:00:00', 'America/Detroit');
$task = new Task(); //eloquent model
$task->due = $timestamp; //where 'due' is cast to datetime
//the due time should be automatically cast to UTC, because it differs from the default
$task->due->toDateTimeString(); //Outputs: 2016-01-01 05:00:00

That being said, it didn't take much to modify the default behavior. I ended up overriding the asDateTime method

<?php namespace App\Traits;

use Carbon\Carbon;

Trait ConvertTimes
{
    /**
     * Return a timestamp as DateTime object.
     *
     * @param  mixed $value
     * @return \Carbon\Carbon
     */
    protected function asDateTime($value)
    {
        if ($value instanceof Carbon) {
            //Timezones match, don't do anything
            if ($value->tzName === config('timezone')) {
                return $value;
            }

            //Timezone is different than the default so change it
            $value->setTimezone(config('timezone'));
            return $value;
        }

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

[jrhenderson1988]

Just to add to this for anyone landing here in the future.

My application is intended to be used internationally and needs to support users all over the world. Therefore, I need to ensure that UTC time is used throughout the entire application. I want to ensure that the database stores times as UTC and I want to access times as UTC as well. I would only ever use a different timezone for display purposes.

It's possible to set the timezone when connecting to the database in Laravel through the configuration. You can do this in the config/database.php file by setting a timezone option. For example:

<?php

return [

    'default' => env('DB_CONNECTION', 'mysql'),


    'connections' => [


        'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE'),
            'username' => env('DB_USERNAME'),
            'password' => env('DB_PASSWORD'),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'case_sensitive_charset' => 'utf8mb4',
            'case_sensitive_collation' => 'utf8mb4_bin',
            'prefix' => '',
            'strict' => false,
            'engine' => null,
            'timezone' => '+00:00'
        ],

You can use a named timezone such as UTC or Europe/London if you want, but you need to make sure that the time zone information tables in the mysql database have been created and populated.

Doing this ensures that the database connection will set that timezone as it connects by running set time_zone="+00:00". So no matter what your server or database server's timezone is set to, this will ensure that all dates are set and received as UTC (+00:00). Take a look in \Illuminate\Database\Connectors\MySqlConnector for more information.

In addition to this, I have a base model class that all of my application's models extend. In this class I have overridden the asDateTime method, as suggested by @andybeesee above, with a bit of a modification:

/**
 * Return a timestamp as DateTime object.
 *
 * @param  mixed  $value
 * @return \Carbon\Carbon
 */
protected function asDateTime($value)
{
    $dateTime = parent::asDateTime($value);

    $timezone = config('app.timezone');

    if (!$dateTime->getTimezone()->getName() !== $timezone) {
        $dateTime->setTimezone(
            new DateTimeZone($timezone)
        );
    }

    return $dateTime;
}

As you can see, I call the parent implementation of asDateTime first, which should ensure that an instance of Carbon or DateTime is returned. This method can accept input in the form of Carbon, DateTimeInterface, strings, integers etc. and returns instances of Carbon, so by calling the parent implementation first, I can account for values that aren't originally instances of DateTime or Carbon. I then compare the returned object's timezone against that of my config's timezone (UTC) and make the necessary adjustments.

This allows my models to accept a DateTime or Carbon object with any timezone for any attribute in my dates cast, and it will automatically be converted to UTC:

$example = new Example();
$example->created_at = new Carbon('2017-06-16 14:05:00', 'Europe/London');
dd($example->created_at->format('Y-m-d H:i:s (T)')); // Outputs "2017-05-16 13:05:00 (UTC)"

[cdarken]

I found a less intrusive solution. I just leave UTC as timezone in app config and use this trait in my models:

trait LocalDateTrait
{
    public function getAttribute($key)
    {
        $localTz = false;
        if (starts_with($key, 'local_tz_')) {
            $localTz = true;
            $key = str_replace('local_tz_', '', $key);
        }

        $attribute = parent::getAttribute($key);

        if ($localTz) {
            $attribute->timezone(config('app.display_timezone'));
        }

        return $attribute;
    }
}

Any date attribute that I access using local_tz_<original_attribute_name> will get converted to the configured display_timezone.

[aliazizi]

That is why I prefer unix timestamp, It's annoying bug in laravel

[slykar]

What @oleynikd said is a big issue.

@taylorotwell you are telling us:

I don't think we should magically convert times to a given timezone.

But this is exactly what Laravel does right now in the example mentioned by @oleynikd. Not exactly to be honest. It replaces timezone information, which is even worse than converting.

I want my system to take any DateTime and normalize it to UTC (or what's configured in Laravel config). The current behavior is really confusing IMO.

[aharp-afg]

I'm very much in agreement with @slykar. Laravel completely ignores the timezone of the Carbon object when saving the model. This is unexpected behavior and, in my opinion, a bug.

@taylorotwell Can this be reconsidered?

[emil-nasso]

I ran into this issue today.

I have a model with an attribute defined as a date.

If I set this attribute as 1999-01-01T10:00:00+03:00 (either as a string or a proper carbon object) it will be stored in the database as 1999-01-01 10:00:00 even though my application is configured to be in UTC.

Eloquent just "throws away" the timezone information and stores an (in my opinion) timestamp.

Can this be reopened?

[driesvints]

@emil-nasso you're responsible for storing datetimes as utc. Convert the time to UTC before you store it.

[emil-nasso]

@emil-nasso you're responsible for storing datetimes as utc. Convert the time to UTC before you store it.

I beg to differ. :) I believe eloquent is responsible for storing data.

I defined an attribute as a date and provided a date encapsulated in a Carbon object to an eloquent object.

In this case, I provided eloquent with the date 1999-01-01 07:00:00 UTC (wrapped in a Carbon object with a +3 tz). This was serialized as 1999-01-01 10:00:00 UTC. Those are not the same.

Eloquent serializes 1999-01-01T10:00:00+03:00 to 1999-01-01T10:00:00+00:00. I really can't see how that would be correct behavior.

The problem is that in asDateTime of HasAttributes the date is instanced as carbon objects and later just serialized.
https://github.com/illuminate/database/blob/master/Eloquent/Concerns/HasAttributes.php#L881

You can't do that without losing information unless you make sure that the date of the timestamp and the application are identical. The problem is not that I have a timestamp of the wrong timezone. The problem is that eloquent claims to support Carbon dates and dates in various formats that contain the timezone but completely disregard the timezone when serializing.

The point of serialization is that you should be able to serialize something and then unserialize it again to get the exact same thing back. The way eloquent serializes datetimes doesn't fulfill that.

[driesvints]

@kylekatarnls can you maybe pitch in on the above?

[lk77]

Hello,

i don't think laravel should convert timezone, they are meaningful, 12H00 +0200 is not the same as 11H00 +0100, even if they have the same utc value, original timezone should be preserved and not lost.

[driesvints]

@lk77 no it shouldn't. Laravel creates timestamp fields for any of its date times which have no context of a timezone. You should convert to UTC before saving and save the timezone separately in a different column.

[driesvints]

@emil-nasso that's incorrect sorry. Laravel has no knowledge of what timezone you're storing your dates in. It mainly uses timestamps which have no context of a timezone. We can't enforce converting to UTC or whatever because people could be assuming the time is stored in a different timezone. You are responsible yourself for converting to the correct timezone before storing any DateTime object (regardless of it being Carbon or not).

Timezones are a presentational concept at best. It's always best to store DateTime objects as UTC for any programmatically action on them and then store the timezone that's related to the timezone as a separate column. Then you can apply the timezone later when presenting it to a user.

Also see: https://www.mysqltutorial.org/mysql-datetime/

MySQL stores TIMESTAMP in UTC value. However, MySQL stores the DATETIME value as is without timezone.

[kylekatarnls]

When storing to a simple DATETIME field, you always loose the timezone information. Either you choose UTC or current timezone of the object. If you assume you can have various timezones for dates to store in the same SQL column, then you can use ->dateTimeTz() type. This will save the date as 2 informations. But the timezone date is an offset. Having the timezone in a separated string column as said @driesvints is even better because string timezone is more precise.

If you have a GMT (UTC) database and an other timezone for your app (config('app.timezone')) you should reconsider the relevance of this divergence. If your app is accessed worldwide or could be someday, then your app should be timezone-agnostic like your DB: 'timezone' => 'UTC' in config/app.php. Then the timezone to display a date can be:

• linked to an other data (for instance a flight: the airport cities)
• device/OS setting (local browser)
• user setting (for instance when sending a email notification)
• custom input (the timezone can be picked in a selector, calculated, and it can be for 1 date of a record or for the whole record)

That means, if Eloquent would proceed a conversion on retrieving dates, it would be highly arbitrary and would break many existing apps.

By not doing any conversion, Eloquent let a wider control application-side to adapt closely to those different cases.

About the conversion on storing. This is actually "too late" to do a conversion IMO. $date->tz(config('app.timezone')) should be done right after getting the input, so when you work with it in your back-end (compare it to now() for instance) you know you're working with aligned dates.