posted 9 years ago

I have users across many timezones. Everything in the database is stored in UTC, no problem.

What I am wondering about though is the best way to handle timezones when you are running complex calculations, eg aggregating data from the database. For example, say a user wants a report generated for the period 2015-08-10 to 2015-08-17 and they are +12 UTC. This report needs to compare each previous days and previous weeks etc and generate statistics accordingly.

In all my SQL statements (eg counting, summing) will I have to run the user input through Carbon to convert to UTC? And then when I want to compare this data (eg compare one day to a previous day), I have to run that also through Carbon to convert to UTC? I'm sure there is some smarter way of avoiding this repetition.

Question 1

Would I be better off converting the user input to UTC time, then handling everything as-is (to keep code clean and me sane), then converting back to user timezone when displaying to the user?

Would I be better off constantly using Carbon to create the correct SQL statements, then just display data to the user.

Question 2

I know about Eloquent's accessors and mutators, but I don't think those help in this situation? Is there some way of applying an accessor rule to the Query Builder? eg say every time I query a date, convert it to x timezone?

Summary

How can I avoid littering my code with calls to Carbon?

Last updated 2 years ago.

monaye

replied 9 years ago

Have you look at query scope?

http://laravel.com/docs/5.1/eloquent#query-scopes

Video also able https://laracasts.com/series/laravel-5-fundamentals/episodes/11

It's little bit different but here is how I use to query to get the post within in this month

    public function scopeThismonth($query)
    {
        $now = Carbon::now();
        $startOfThisMonth = Carbon::instance($now)->startOfMonth();
        $currentOfThisMonth = Carbon::instance($now)->subSecond();

        $query = $query->whereBetween('created_at',
                    [$startOfThisMonth, $currentOfThisMonth]);

        return $query;

    }


// This is how I get how many post is posted this month. 

Post::thismonth()->count();

silvanite

replied 9 years ago

I'm also struggling to perform date calculations. The problem I have is that I'm trying to filter my query to only show records where the time difference between two dates within the database matches my criteria. For example, only show articles where updated_at is at least once month newer than created_at. I have tried whereRaw to use date_add functionality, but nothing seems to work within the scope?