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.

Reply to

Use Markdown with GitHub-flavored code blocks.