MySQL/Eloquent: Grouping by date in users timezone

Posted 1 year ago by robjbrain

This may be able to be answered simply as a MySQL rather than a Laravel specific answer, although of course I am using Laravel and Eloquent so if there's a method within Laravel\Eloquent to do this that would be awesome.

My database is in UTC. I want to get a count of how many comments where posted each day so show on a graph, so I do something like this:

select DATE(created_at) as date, count(*) as count from `social_forum_comments` group by `date`

Very simple, this works perfectly for me.

However, if someone is in Australia, the counts won't accurately reflect a day in THEIR timezone, but a day in UTC.

I'll try and re-iterate:

What I want is the count between these dates: "2017-11-05 00:00:00" and "2017-11-05 23:59:59" in the users local timezone.

With the above query if an Australian is on the site, it will be showing a count from "2017-11-05 10:00:00" and "2017-11-06 09:59:59" (where the users timezone is UTC +10)

If it was just one query that may be easier to do, but since I want it grouped by day and showing a whole month (for a graph) i'm not sure how that would be possible?

Is there a way to do this via Eloquent? perhaps setting the timezone on connection or something like that?

Please sign in or create an account to participate in this conversation.