When returning Laravel results to the user, the time zone is displayed incorrectly.

Question

I have a Laravel API and the application's timezone is set to UTC. MySQL's time zone is set to SYSTEM, which is also expressed as UTC. I also have a column in the users table that stores the user's time zone information.

My front-end interface has a field where the user can enter a date/time as the time of a certain process, and this time will be sent to the API in the user's time zone. So when that record is saved, I convert it to UTC and then insert it into the database like this;

auth()->user()->procedure()->create([
    'procedure_time' => Carbon::parse($request->procedure_time, auth()->user()->timezone)->setTimezone('UTC'),
]);

I'm trying to get records of all user processes for the current day in the current user's time zone and return those results to the frontend. I thought the results were correct, but when my computer got to midnight, the results were still showing as records from the previous day for about 10 hours or so. This is how I try to return these results;

$todayProcedures = Procedure::query()
    ->where('user_id', $user->id)
    ->whereDate('procedure_time', '>=', Carbon::now()->startOfDay()->tz($user->timezone))
    ->whereDate('procedure_time', '<=', Carbon::now()->endOfDay()->tz($user->timezone))
    ->get();

I can't figure out where I went wrong.

Answer 1

I used the whereBetween method and converted the start and end of the day using the user's time zone. This way the database will be queried based on the user's local time and you should be able to get the correct results.

$todayProcedures = Procedure::query()
    ->where('user_id', $user->id)
    ->whereBetween('procedure_time', [
        Carbon::now()->startOfDay()->setTimezone($user->timezone)->toDateTimeString(),
        Carbon::now()->endOfDay()->setTimezone($user->timezone)->toDateTimeString(),
    ])
    ->get();

When dealing with timestamps in your application, it is important to handle time zone conversions correctly. To ensure consistency and accuracy, please follow these guidelines:

1. Database Storage: Always store timestamps in the database using UTC format. Doing this ensures that all timestamps are standardized, independent of any specific time zone.
2. Query time zone conversion: Time zone conversion is performed when querying the database, not when records are inserted or updated. By doing this, you ensure that the data retrieved from the database is consistent and accurate regardless of the user's time zone.
3. User Time Zone Based Display: Perform necessary conversions when displaying timestamps to users or processing based on their time zone preferences. By adjusting the timestamp to the user's time zone, you can provide a more user-friendly and localized experience.

Follow these guidelines and your application will maintain accurate timestamp data and provide consistent results across different time zones.