MyApp, UTC, User's Timezone, My Nightmare

Hi,

1. You state you store everything in UTC timezone.
2. How did you set your database dates in the database? Be totally specific. Done in PHP on server, done in database via defaults. Be specific.
3. How you know the dates in your database are in UTC time.
4. What is the column definition in the database schema, not your Eloquent Model, the database.
5. Forget the time portion, let the database do the actual compares and date math. It will select and order them for you.
6. If your are storing the dates in UTC, forget what the user's Timezone is except to know the Users date. You only need the date, dates time don't have timezones. Dates go from 0:00:00 to 23:59:59 hours, again the database can handle that.

@jimmck

2 - How did you set your database dates in the database? Be totally specific. Done in PHP on server, done in database via defaults. Be specific.

2a) Within config/app.php

    /*
    |--------------------------------------------------------------------------
    | Application Timezone
    |--------------------------------------------------------------------------
    |
    | Here you may specify the default timezone for your application, which
    | will be used by the PHP date and date-time functions. We have gone
    | ahead and set this to a sensible default for you out of the box.
    |
    */

    'timezone' => 'UTC',

3 - How you know the dates in your database are in UTC time.

3a) Because when I check the timestamp it reflects UTC time.

4 - What is the column definition in the database schema, not your Eloquent Model, the database.

4a) The definition proposed by Laravel with the created_at and updated_at timestamps

5 - Forget the time portion, let the database do the actual compares and date math. It will select and order them for you.

5a) Ok how?

6- If your are storing the dates in UTC, forget what the user's Timezone is except to know the Users date. You only need the date, dates time have timezones. Dates go from 0:00:00 to 23:59:59 hours, again the database can handle that.

6a) Ok! I'm using carbon everywhere already. What do you advise?

Thank you for your answer, I will appreciate more help :)

I have had similar issues with dates. I don't have a final solution yet, but my workaround could help you.

trait LocalizedDate

namespace App;

use Carbon\Carbon;


trait LocalizedDate
{
    /**
     * Timezone for front end representation
     * @var string
     */
    public $tz = 'Europe/Madrid';

    public $locale = 'es';

    /**
     * Creates a Carbon instance from the attribute value
     * taking into account the local timezone
     *
     * @param $value
     * @return Carbon
     */
    protected function asLocalizedDate($value)
    {
        return $this->asDateTime($value)->timezone($this->tz);
    }

    /**
     * Parses the given value to set an attribute and normalizes it
     * to the format used by the database
     *
     * @param $value
     * @return null|string
     */
    protected function parseLocalizedDate($value)
    {
        if ($value instanceof Carbon) {
            return $this->toUTCDateTimeString($value);
        }

        if ($value == null || $value == '') {
            return null;
        }

        if (preg_match('/^(\d{2})\/(\d{2})\/(\d{4})$/', $value)) {
            return $this->toUTCDateTimeString(
                Carbon::createFromFormat('d/m/Y', $value, $this->tz)
            );
        }

        if (preg_match('/^(\d{2})\/(\d{2})\/(\d{4})/', $value)) {
            return $this->toUTCDateTimeString(
                Carbon::createFromFormat('d/m/Y H:i', $value, $this->tz)
            );
        }

        if (preg_match('/^(\d{4})-(\d{2})-(\d{2}) (\d{2}):(\d{2})$/', $value)) {
            return $this->toUTCDateTimeString(
                Carbon::createFromFormat('Y-m-d H:i|', $value, $this->tz)
            );
        }

        return $this->toUTCDateTimeString(
            Carbon::createFromFormat('Y-m-d H:i|+', $value, $this->tz)
        );
    }

    /**
     * Gives the date in string format for the DB format in UTC timezone
     *
     * @param Carbon $date
     * @return string
     */
    private function toUTCDateTimeString($date)
    {
        return $date->setTimezone('UTC')->toDateTimeString();
    }
}

And in the model where you have dates:

class Congress extends Model
{
    use LocalizedDate;

    /**
     * Returns Carbon instance of the start date attribute taking into account the timezone
     *
     * @param $value
     * @return \Carbon\Carbon
     */
    public function getStartAttribute($value)
    {
        return $this->asLocalizedDate($value);
    }

    /**
     * Returns Carbon instance of the end date attribute taking into account the timezone
     *
     * @param $value
     * @return \Carbon\Carbon
     */
    public function getEndAttribute($value)
    {
        return $this->asLocalizedDate($value);
    }

    /**
     * Sets the value of the start date attribute taking into account the timezone
     *
     * @param $value
     * @return \Carbon\Carbon
     */
    public function setStartAttribute($value)
    {
        $this->attributes['start'] = $this->parseLocalizedDate($value);
    }

    /**
     * Sets the value of the end date attribute taking into account the timezone
     *
     * @param $value
     * @return \Carbon\Carbon
     */
    public function setEndAttribute($value)
    {
        $this->attributes['end'] = $this->parseLocalizedDate($value);
    }
}

That way my dates have the correct timezone and when I compare dates Carbon takes the timezones into account.

And answering your question, yes, you have to use DateTime instead of just Date.

In my example I have hardcoded the timezone because it is the same for all the users. But it shouldn't be difficult to set it to whatever timezone a specific user has.

@otepas thank you but out of the box it may be complex to me :)

protected function asLocalizedDate($value)
    {
        return $this->asDateTime($value)->timezone($this->tz);
    }

Where is the asDateTime() signature?

@jrean asDateTime is a method of Eloquent Model

/**
     * Return a timestamp as DateTime object.
     *
     * @param  mixed  $value
     * @return \Carbon\Carbon
     */
    protected function asDateTime($value)

@jrean Stop using created_at, always use updated_at. When a record is first added, created_at and updated_at are equal. For last profile at try the max of updated_at for that Date.

@jimmck I need to rely on created_at because the record can't be updated and even if it will be I still want to rely on the created_at.

@otepas thank you (I didn't know it) :)

@jrean Fine if they are not updated they always will be equal. Using the laravel users table this query last registered user for a given date.

select max(updated_at) from laravel.users 
        where updated_at >= '2015-07-20 00:00:00' 
                and updated_at <= '2015-07-20 23:59:59'

Just plug in the date you want. No playing with time. Switch to min for the first Register.

Ok so the correct answer according to my first post seems to be:

For instance, to work on "today"

• Define the start of the day for the user based on its timezone

$todayForUser = Carbon::today($user_timezone);

• Convert the start of the day in UTC

$utcTodayStart = $todayForUser->copy()->timezone('UTC');

• Convert the end of the day in UTC

$utcTodayEnd = $todayForUser->copy()->endOfDay()->timezone('UTC');

Or compacted:

        $utcTodayStart = Carbon::today($profile->timezone)->timezone('UTC');
        $utcTodayEnd   = Carbon::today($profile->timezone)->endOfDay()->timezone('UTC');

Then I can run my query on the appropriate range with:

->whereBetween('created_at', [$utcTodayStart, $utcTodayEnd])

:)

@jrean I had this exact issue on a project last year. Users had accounts, and one of the things they could configure was timezone as the app was to be used by students in campuses across the world and in different timezones.

The way I solved it was with a trait that I applied to my models. On save, it would convert dates to UTC time so that they entered the database as UTC. On retrieval, they were converted to the user’s locale timezone (which would be the one they’d set in their account, fallback to the timezone sent by the user’s browser, before ending on the app.timezone configuration value.

@martinbean thank you :) Just to confirm, when you run some queries and you need to use date/datetime you take the user's date/datetime based on their timezone then convert to UTC before running the query, right?

The query should return object(s) with timestamps in UTC then on your views or whatever you use, you display the timestamps with the user's timezone?

@jrean The user would submit dates and times in their local timezone. The trait would then convert that to UTC just before saving, so when it went in the database it went in at UTC time. Fetching records I’d just do the opposite: for any dates and times, detect the user’s timezone and convert from UTC to that timezone.

@martinbean last question.

The user would submit dates and times in their local timezone

Why you just didn't simply use the Application Timezone config option to "force" UTC? I mean, what was the reason of your choice?

    /*
    |--------------------------------------------------------------------------
    | Application Timezone
    |--------------------------------------------------------------------------
    |
    | Here you may specify the default timezone for your application, which
    | will be used by the PHP date and date-time functions. We have gone
    | ahead and set this to a sensible default for you out of the box.
    |
    */

    'timezone' => 'UTC',

Why you just didn't simply use the Application Timezone config option to "force" UTC? I mean, what was the reason of your choice?

@jrean Because if a user submits a date-time of 2015-07-20 03:00:00, but they’re 8 hours ahead of UTC, then the date-time they’re submitting should be stored as 2015-07-19 19:00:00 in the database. When that date-time is retrieved, if the user’s timezone is still UTC+8, then it’ll be converted back.

@martinbean ok thank you! I have not enough experience (for now). I used the UTC in my timezone settings. So as you say when a user add a record (I don't care of its timezone, I just create the record in UTC). When I need to fetch records I use the Carbon::now($user_timezone) or Carbon::today($user_timezone) then I convert to ->timezone('UTC') before running the query. Then when I want to display data I convert (again) based on the user's timezone.

For me it works (until I discover I messed up :D)

I'm having issue with Timezones too.. upto Lumen or Laravel models timezones are set to UTC (as in default configuration set, and I want that too)

But when record gets saved in database, it converts automatically to MySQL server's default timezone.

What should I do in this case?

I've got that fixed.. I changed mysql field type from "timestamp" to "datetime" and Date/Times are saving in UTC (as per config)

I realise this is old, but do watch out for statements like this:

$to = $from->addDay();

Carbon (for now) is not immutable and this statement will add a day to $from, then assign $to to the same value. The result is that both $from and $to will be a day later. Clone $from first to avoid this problem:

$to = $from->copy()->addDay();