Search code examples
phpmysqllaravel-5.3

Time Zone Conversion in Laravel Eloquent

I have the following line in an eloquent query:

->whereBetween('issue_date', [$fiscal_start, $fiscal_end])

$fiscal_start and $fiscal_end are in the user's local time zone, and issue_date is (in the table) stored in UTC. All three are timestamps.

$fiscal_start example:

Carbon {#639 ▼
  +"date": "2016-07-01 00:00:00.000000"
  +"timezone_type": 3
  +"timezone": "Australia/Sydney"
}

$orgTz contains "Australia/Sydney"

What is the correct syntax for converting the issue_date in the whereBetween statement to local timezone before comparison?

I have tried a few variations of this unsuccessfully:

->whereRaw("CONVERT_TZ(issue_date, 'UTC', {$orgTz}) BETWEEN {$fiscal_start} AND {$fiscal_end}")
Solution

  • I guess I was missing a couple of '

    ->whereRaw("CONVERT_TZ(issue_date, 'UTC', '{$orgTz}') BETWEEN '{$fiscal_start}' AND '{$fiscal_end}'")