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?