A time tracking entry is stored. Six months later somebody in another country opens the report and the hours have moved by five and a half. Nothing was edited. The database was not touched.

This is the most common data bug in any application that records when something happened, and it is almost always introduced on the first day, by somebody writing datetime without thinking about it.

The three things people mean by “the time”

Before any schema decision, be clear which of these you are storing. They need different columns and they behave differently.

• An instant. A moment that happened. A clock-in, a screenshot, a log line. Unambiguous worldwide.
• A wall-clock time. “The office opens at 9am” — which is 9am wherever the office is, forever, regardless of offsets.
• A date with no time at all. A leave day, an invoice date. Converting it to a time zone is how it ends up on the wrong day.

Almost every bug in this area comes from storing one of these as another.

Store instants in UTC, always

For anything that happened, store UTC. Not the user’s time zone, not the server’s. UTC, converted at the edge when you display it.

-- MySQL
`started_at` DATETIME NOT NULL,   -- always UTC, by convention
`ended_at`   DATETIME NULL

MySQL’s TIMESTAMP type converts on read and write using the session time zone, which sounds helpful and is the source of the bug. DATETIME stores exactly what you give it and gives back exactly that — which is what you want, provided everything that writes to it writes UTC.

In Laravel, that is the default as long as you leave it alone:

// config/app.php
'timezone' => 'UTC',

Changing that line to Asia/Kolkata is the single most common cause of this bug in Indian codebases. It looks like a localisation setting. It is a storage setting.

How to tell if yours is already wrong

You do not need to read the code. Three checks:

-- 1. what does the database think the time is?
SELECT NOW(), UTC_TIMESTAMP(), @@session.time_zone, @@global.time_zone;

If NOW() and UTC_TIMESTAMP() differ and your columns are TIMESTAMP, every read is being converted by the session.

-- 2. does a stored value match what the app thinks it stored?
SELECT id, started_at FROM time_entries ORDER BY id DESC LIMIT 1;

Compare it to the time the entry was actually created, in UTC. A difference equal to an offset is the answer.

Third: change your machine’s time zone, reload a report, and see whether historic rows move. If they do, the conversion is happening somewhere it should not.

Wall-clock times are not instants

A work schedule that says the day starts at 09:00 is not a UTC instant. Stored as one, it drifts when daylight saving changes and is wrong for anybody in a different zone.

`day_start` TIME NOT NULL,          -- 09:00:00, a wall clock
`day_end`   TIME NOT NULL,
`timezone`  VARCHAR(64) NOT NULL    -- 'Asia/Kolkata' — the zone it means

Store the local time and the zone it belongs to, separately. Combine them only at the moment you need an instant:

$start = CarbonImmutable::createFromFormat(
    'Y-m-d H:i:s',
    $date->format('Y-m-d').' '.$schedule->day_start,
    $schedule->timezone
)->utc();

Dates with no time

A leave day is a date. An invoice date is a date. Storing them as DATETIME and converting produces the classic off-by-one where leave on the 3rd appears on the 2nd for half the team.

`leave_date` DATE NOT NULL   -- not DATETIME, and never converted

In Laravel, cast it so nothing helpfully applies a time zone:

protected function casts(): array
{
    return ['leave_date' => 'date'];   // not 'datetime'
}

The user’s zone belongs on the user

Display conversion needs a zone per person, not a global setting:

`timezone` VARCHAR(64) NOT NULL DEFAULT 'Asia/Kolkata'

Use the IANA identifier, never an offset. +05:30 is a fact about one moment; Asia/Kolkata is a rule that knows about historical changes and daylight saving. Storing offsets is how a system becomes wrong on exactly two days a year, in a way nobody can reproduce in March.

$entry->started_at
    ->setTimezone($user->timezone)
    ->format('d M Y, H:i');

The day boundary problem

Reports group by day, and a day is a local concept. Grouping UTC timestamps by UTC date puts an Indian team’s evening work on the wrong day, every day.

-- wrong: groups by UTC date
SELECT DATE(started_at), SUM(seconds) FROM time_entries GROUP BY 1;

-- right: convert to the organisation's zone first
SELECT DATE(CONVERT_TZ(started_at,'+00:00','+05:30')), SUM(seconds)
FROM time_entries GROUP BY 1;

CONVERT_TZ with named zones requires MySQL’s time zone tables to be loaded, which on shared hosting they frequently are not. Check before relying on it:

SELECT CONVERT_TZ(NOW(),'UTC','Asia/Kolkata');   -- NULL means the tables are not loaded

If it returns NULL, either load the tables or do the conversion in application code, where the zone database travels with PHP.

Querying a range that means a local day

“Everything on the 3rd” means the 3rd in somebody’s zone. Build the boundaries in that zone and convert them, rather than converting every row:

$tz    = $organisation->timezone;
$start = CarbonImmutable::parse($date, $tz)->startOfDay()->utc();
$end   = $start->addDay();

TimeEntry::whereBetween('started_at', [$start, $end])->get();

This is also much faster. Converting the column kills the index; converting two bounds does not.

Daylight saving, which will reach you

India does not observe it, which lulls Indian codebases into never testing it. The first European or North American user finds it within six months.

Two days a year a local day is 23 or 25 hours long. Code that adds 86400 seconds to get “tomorrow” is wrong on those days:

// wrong on the two transition days
$tomorrow = $today->addSeconds(86400);

// right — calendar arithmetic in the local zone
$tomorrow = $today->setTimezone($tz)->addDay()->startOfDay()->utc();

Testing it properly

Three tests catch almost everything, and all three are cheap:

public function test_entry_stores_utc_regardless_of_app_timezone(): void
{
    config(['app.timezone' => 'Asia/Kolkata']);
    $e = TimeEntry::factory()->create(['started_at' => '2026-10-06 18:30:00']);
    $this->assertSame('2026-10-06 18:30:00',
        $e->fresh()->started_at->utc()->format('Y-m-d H:i:s'));
}

Then one that runs a daily report with the organisation in Asia/Kolkata and an entry at 23:00 local, asserting it lands on the local date and not the UTC one. And one that crosses a daylight saving boundary in a zone that has one — Europe/London on the last Sunday in March is the standard case.

Migrating a database that is already wrong

If times were stored in local time, the fix is a one-off conversion, and it has to be done once and exactly:

UPDATE time_entries
SET started_at = CONVERT_TZ(started_at,'+05:30','+00:00'),
    ended_at   = CONVERT_TZ(ended_at,  '+05:30','+00:00')
WHERE id <= :max_id_before_the_fix;

The WHERE clause matters more than the conversion. Deploy the code fix first, note the highest id written under the old behaviour, and convert only up to it. Without that bound, a retry converts some rows twice and you will not be able to tell which.

A short answer

Instants in UTC in DATETIME, never TIMESTAMP. Wall-clock times as TIME plus an IANA zone. Dates as DATE, never converted. The zone on the user and the organisation, never an offset. Build day boundaries in the local zone and convert them, not the column. Test one entry, one report and one daylight saving boundary.

Get those right on day one and the question never comes up again. Get them wrong and every report is quietly off by an offset that nobody can find.