Search code examples
phplaraveldatelaravel-5php-carbon

Laravel 5 / Carbon: Count number of records for specific range in a different timezone

I store my records in EST timezone rather than UTC as I almost never need to use UTC. Recently though I have needed to generate a report in UTC.

I count the number of "clicks" on my site, this is how I grab yesterdays click total: Click::where('created_at', '>=', Carbon::now()->yesterday())->where('created_at', '<=', Carbon::now()->startOfDay())->count();

That works great, but now I need to get the click total for "yesterday in UTC" -- is there an easy way to do this using eloquent / carbon?

Solution

  • Assuming that your records in your database are stored as EST as you mention then you will need to do the following.

    // Get the start and end times you want in UTC
$start = Carbon::yesterday('UTC');
$end = Carbon::yesterday('UTC')->endOfDay();

// Convert those times to EST
$start->timezone('EST');
$end->timezone('EST');

// Now query the number of clicks, 'whereBetween' is a great little shortcut 
// for querying over a range
Click::whereBetween('created_at', [$start, $end])->count();

    Note that carbon is a fluent API so you could simplify this to;

    $start = Carbon::yesterday('UTC')->timezone('EST');
$end = Carbon::yesterday('UTC')->endOfDay()->timezone('EST');

    Depends entirely how you want your code to read.

    As an aside the Carbon::now() and Carbon::yesterday() builders will use the default timezone specified in your php.ini if it is not supplied.