MySQL/Eloquent: Grouping by date in users timezone

I believe you can convert the timezones while selecting the data to another timezone, that might be what your looking for: https://stackoverflow.com/questions/15017799/how-to-convert-utc-date-to-local-time-zone-in-mysql-select-query

However it's still a hard proces. Just thinking out of the box, but wouldn't it be better to give it a where in statement and select the count per day? This way you have control over what timezone you pick.

Also wondering why you want to display it differently? I mean when I reply today on 7:34. It might be a different time in Australia, but they still see the comment right away right? For them I still replied the same day! Not really sure if your making it harder than it should be. That's my 2 cents

Well if you're in Australia and you say I want to know to how many XXX happened on November 7th, you care about your November 7th not British, French or American November 7th right, they mean nothing to you.

I'm not sure if i've misunderstood you or you've misunderstood me on that last paragraph :)

Thanks for the link, that works perfectly actually, albeit not that attractive to look at.

select DATE(CONVERT_TZ(created_at,'+00:00','+10:00') as date, count(*) as count from `social_forum_comments` group by `date`

In Laravel it seems like I can do this:

$results = DB::table('social_forum_comments')
    ->select(DB::raw("DATE(CONVERT_TZ(created_at,'+00:00'," . $user->getTimezoneOffset() . ") as date, count(*) as count"))
    ->groupBy('date')
    ->get();

$stats = collect($results)->pluck('count', 'date');

// And in the user class
// Timezone is a user defined column on the user table
// $timezone = "Australia/Brisbane"
class User {
    public function getTimezoneOffset() {
        return Carbon::now()->setTimezone($this->timezone)->format('P');
    }
}

I'm not sure if that's actually the best way to do it, it's certainly not the most 'eloquent' but it works. I couldn't figure out a better function for getting the offset and there's probably no need to use Carbon instead of just DateTime but i'm just used to using Carbon by default now.

Actually i've just discovered if you load the timezones into mysql then you don't need to work out the offest and can just do:

// $user->timezone = 'Australia/Brisbane'
$results = DB::table('social_forum_comments')
    ->select(DB::raw("DATE(CONVERT_TZ(created_at,'UTC','" . $user->timezone . "') as date, count(*) as count"))
    ->groupBy('date')
    ->get();

$stats = collect($results)->pluck('count', 'date');

Yes indeed! I wasn't sure if you were after this but if this works for you than great!

@robjbrain hey!

Making the timezone calculation in your query is indeed a way to go about that, however it might get slower and slower as your database grows. Think about it, mysql isn’t magical. For reach row in your database it will convert the date to the correct timezone and then compare it. This needs a little bit more research, but I’m not sure mysql will use the index on the “created_at” column in that situation as the index is only correct for UTC timezone.

I would like to suggest an alternative way to achieve that while ensuring mysql can use an index to perform the search.

If you know the user’s timezone, you can then calculate what is the right “UTC” time that reflects their timezone. For example, if you want all data between “2017-11-05 00:00:00” and “2017-11-05 23:59:59” in the user’s timezone and you know their offset is +10hours, you can convert your UTC time t reflect that and look for any comments between “2017-11-04 14:00:00” and “2017-11-05 13:59:59”.

If you use “Carbon” this gets as easy as calling a timezone set method on the object.

After you get that done, you can use Eloquent’s “whereBetween” method to fetch those records.

Let me know if you need code samples :)!

@robjbrain You can read a little bit more about it here: https://kfirba.me/blog/querying-against-dates-and-timezones-with-eloquent