convert datetime to a new timezone

Using Carbon, you take the input $dateInCET:

$datetimeInCst = Carbon::parse($dateInCET, 'Europe/Berlin')->setTimezone('CST');

So your code would look like:

public function range($start_date, $end_date)
{
    $start_date = Carbon::parse($start_date, 'Europe/Berlin')->setTimezone('CST');
    $end_date = Carbon::parse($end_date, 'Europe/Berlin')->setTimezone('CST');

    $records = API::where('report_date', '>=', $start_date)->where('report_date', '<=', $end_date)->get();

    return [
        'start_date' => $start_date,
        'end_date' => $end_date,
        'records' => $records,
    ];
}

You could extract a method to convert timezones

ok. Added that and it works but I need the second half of the issue. How do I convert the output?

What output do you want to convert; the recorded_at property from the Eloquent query? You can make an Accessor method on the Eloquent model to convert this for you. But this hard-codes the original and new timezones:

// Api Model

public function getRecordedAtAttribute()
{
    return Carbon::parse($this->attributes['recorded_at'], 'CST')->setTimezone('Europe/Berlin');
}

Note that this will modify the recorded_at property that the model returns - which may not be desirable. In such cases, you can create a new property e.g. recorded_at_cet by naming the accessor method appropriately getRecordedAtCetAttribute(). It is returning a Carbon instance; which you might want to format() back to a string by chaining an appropriate method from the Carbon docs

This is the database table that we're querying in the API::where statement. I need that recorded_at column to be returned in CET timezone.

CREATE TABLE `api_data` (
  `hit_id` bigint(22) unsigned NOT NULL AUTO_INCREMENT,
  `report_date ` datetime NOT NULL,
  `affiliate_id` int(11) NOT NULL,
  `sid` int(11) NOT NULL,
  `c1` varchar(255) NOT NULL,
  `c2` varchar(255) NOT NULL,
  `c3` varchar(255) NOT NULL,
  `ip_address` bigint(20) NOT NULL,
  `amount` decimal(8,2) NOT NULL,
  `creative_id` bigint(20) NOT NULL,
  `tracking_token_id` bigint(20) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`hit_id`),
  KEY `report_date` (`report_date`,`sid`)
) ENGINE=InnoDB AUTO_INCREMENT=70187378437 DEFAULT CHARSET=ascii;

Carbon::parse($end_date, 'Europe/Berlin')->setTimezone('CST')->format('d/m/Y');

->format() uses regular php date formatting options.

Ah, you added stuff while I posted that. You mean when you retrieve the recorded_at from the db, you need to convert it to a different tz? You can use an accessor for that.

https://laravel.com/docs/5.4/eloquent-mutators#accessors-and-mutators

Is getReportDateAttribute() automagically called?

Right now I'm just doing the

$records = API::where()->where()

Will I need to wire anything up to make getReportDateAttribute() be called?

The accessor method getRecordedAtAttribute() will respond to a message on a model instance for recorded_at, i.e.

$api->recorded_at // returns the result of getRecordedAtAttribute()

No, it's automatic.

I just tested and it's not automatically changing the value of the column. Do I need to iterate through the result set and change the value of the column in each row?

No, it should work automatically so long as you are dealing with Eloquent models; if you are using the query builder, then the accessor method cannot be used.

Just looking back over the schema, and the previous answers, I may have been confused about the column name - where I have written recorded_at should read report_date?

That was it. The naming caused it. I had mixed you up. Sorry. Very cool feature of Laravel. The more I know about it, the more I like it.

@jbrahy Just as an aside, you can shorten your code (and make it a tad more readable) using ->whereBetween()

//$records = API::where('report_date', '>=', $start_date)->where('report_date', '<=', $end_date)->get();

$records = API::whereBetween('report_date', [$start_date, $end_date])->get();