Exporting Records with Timezone Adjustments

As a rule, dates in a database are stored in UTC. So, when you export records from database constrained by dates, it is a good idea to use appropriate timezone in export query. You can do it in one of two ways. One way is create incoming dates in user’s timezone, then covert time to UTC and query the database. Another way is to convert database column (created_at) to the users timezone and query against it.

The first approach. First we need to convert time coming to us from the user into the UTC.

$startAtUtc = (new Carbon($this->request->input('start_at'), $this->timezone))->startOfDay()->setTimezone('UTC');
        $endAtUtc = (new Carbon($this->request->input('end_at'), $this->timezone))->startOfDay()->setTimezone('UTC');
        $query = Orders::whereBetween('created_at', [$startAtUtc, $endAtUtc]);

Resulting query will look like so:

Start at UTC: 2020-07-14 07:00:00
End at UTC: 2020-08-27 07:00:00
Export query: select * from `orders` where `created_at` between ? and ?

The second approach. First we need to get timezone offset and then we do the query.

$timezoneOffset = Carbon::now()->setTimezone($this->timezone)->format('P');

        $query = Orders::whereRaw("convert_tz(created_at, '+00:00','" . $timezoneOffset . "') between '" . (new Carbon($this->request->input('start_at'), $this->timezone))->startOfDay() . "' and '" . (new Carbon($this->request->input('end_at'), $this->timezone))->startOfDay() . "'"
        );
       $query->toSql();

This is how the query will look like:

select * from `orders` where convert_tz(created_at, '+00:00','-07:00') between '2020-07-27 00:00:00' and '2020-08-27 00:00:00'