Get data filtering by local timezone from UTC

Question

We have an application in Laravel 5.2. In our database, published_at stores date in timestamp with carbon instance in UTC, just as created_at and updated_at.

Every post is supposed to be published at midnight in every timezone. We saved our dates data in UTC format, like

'published_at' => '2016-04-12 00:00:00'

What we want to do is like;

if a person in US views it, then he/she sees that post after his/her midnight time. If a person in China views it, then he/she sees that post after his/her midnight time. How can we achieve this?

This is what we are doing at the moment. But, we think it's not gonna work.

    public function all($limit, array $data = [])
    {
        $posts = $this->post->with('categories')
            ->where(
                'published_at', Carbon::now()->setTimezone($data['user_timezone'])
                                             ->format('Y-m-d 00:00:00')
            )
            ->orderBy('published_at', 'ASC')
            ->paginate($limit);
    }

This is the first time we are working in timezones and we have no idea. Any help would be greatly appreciated. Thank you.

Answer 1

It sounds like you're trying to do a "sequential timezone publish", where over the course of 24 hours, it's midnight somewhere.

Assuming you've grabbed the timezone from input somewhere (in $data?), you can use the standard Eloquent / Laravel Query Builder verbiage to construct the query you require:

https://laravel.com/api/master/Illuminate/Database/Query/Builder.html

    public function all($limit, array $data = [])
    {
        $posts = $this->post->with('categories')
            ->where(
                'published_at',
                '<',
                Carbon::now($data['user_timezone'])
                    ->format('Y-m-d H:i:s')
            )
            ->orderBy('published_at', 'ASC')
            ->paginate($limit);
    }

That way as soon as "midnight" has occurred in the timezone being passed through, the "published_at" will have a value of "less than" the current timestamp, including it in the results.