A Five-and-a-Half-Hour Gap: When PHP and MySQL Keep Two Different Clocks

A customer messaged that the festival discount code still worked for them. The festival had ended at midnight and at that moment it was one in the morning. We went to the coupon record: the expires_at column was exactly the value it should be. The admin panel next to that same coupon said "expired". But the query that ran when the code was applied considered the coupon valid and applied the discount.

For hours we looked for a bug in the discount logic. We read the code line by line, tested the conditions, and even suspected the cache. Until we put two outputs side by side: the value of NOW() in the database and the value of now() in PHP, at the same instant. The difference was exactly five hours and thirty minutes.

That number is familiar: it is the distance between the local zone and UTC. That means neither of the two was broken; each worked with a different clock and nobody had told the other. This article is about exactly that time zone mismatch between PHP and MySQL: how it shows itself, how you diagnose it in ten seconds, and what architecture stops it from coming back. (The numbers here use a zone at UTC+5:30; use your own offset in your head.)

The symptom: an expired discount code that is still accepted#

The symptoms of this bug are so scattered that you usually file them as several separate bugs. These are the things we saw:

• A discount code that is "expired" in the panel is still applied on the checkout page — or the reverse, a code that still has hours of validity is rejected.
• A rate limiter that was supposed to block for ten minutes opens much too early or much too late.
• A record whose created_at is ahead of "now". An order that seems to come from the future.
• A countdown on the page that does not match the real clock and is always wrong by the same fixed amount.
• A "today's sales" report that counts the first hours of the morning as yesterday.

The common thread of all these is one number: five hours and thirty minutes. Not a second more, not a second less. Whenever you see a time gap that is constant and happens to equal your zone's distance from UTC, do not look for a logic bug. Two clocks are running.

The worst part is that the record is saved correctly. The data is not corrupted, only the comparison is. So if you look at the record you see nothing; you have to look at what the comparison was made against.

The cause: two independent clocks in PHP and MySQL#

In a simple web request at least two independent clocks are involved and each is configured separately:

1. PHP's clock, which comes from the date.timezone directive in php.ini. Anything in your code that builds date(), time(), DateTime or Carbon reads from this clock.

1. MySQL's clock, which is the time_zone variable and has two levels: @@global.time_zone for the whole server and @@session.time_zone for this very connection. Its default is usually SYSTEM, meaning "whatever the operating system clock of the database server says". Anything you write in SQL — NOW(), CURDATE(), CURRENT_TIMESTAMP — reads from this clock.

These two have no connection to each other. Setting one does not move the other. On shared hosting you (or the host) have usually set date.timezone to your local zone (because dates must display correctly) but MySQL's time_zone stayed untouched at SYSTEM and the server's operating system is on UTC. The result: PHP writes in local time, MySQL compares in UTC.

Now the path of the bug is clear. When Laravel saves a record, it builds the date string itself and sends it to the database — so that string was built with PHP's clock. But when you write a query with expires_at > NOW(), the comparison happens inside MySQL itself and NOW() is filled with MySQL's clock. One side of the comparison came from local time and the other from UTC. A gap of hours, right there.

And the reason it is so confusing is that both paths "work". No error, no exception, no log. The site is healthy; only the decisions that involve time are slightly wrong. If your team works in one zone and does not work at night, you may not see this bug for months; because for most hours of the day, a shift of a few hours still keeps you on the same "day". The bug only pops out near boundaries: midnight, the moment of expiry, the end of a time window.

Diagnosis: see the real time zone of both#

Before any change, read the real value of both clocks. Do not guess. This query says everything at once:

SELECT @@global.time_zone, @@session.time_zone, NOW(), UTC_TIMESTAMP();

Reading it is simple:

• If NOW() and UTC_TIMESTAMP() are equal, your session is on UTC.
• If NOW() is hours ahead, the session is on a local offset.
• If @@session.time_zone is SYSTEM, the real clock is that of the database server's operating system — which may not be the same as the web server's operating system, even if they are on one machine.

The PHP side is just as simple:

echo date_default_timezone_get();   // for example Asia/Kolkata or UTC
echo (new DateTime())->format('c'); // the full value with offset
echo ini_get('date.timezone');      // the raw php.ini value

And in Laravel, with php artisan tinker put both side by side at one instant:

[
    'php' => now()->toIso8601String(),
    'app_tz' => config('app.timezone'),
    'db' => DB::selectOne('SELECT NOW() AS n, @@session.time_zone AS tz'),
];

If the output of this piece does not match what you expected, the bug is found. One important point: command-line PHP and web PHP can read from two different php.ini files. On cPanel this difference is common. So run the same check from a browser once too, not only from the terminal. If the cron output differs from the terminal output, you are probably dealing with the alias trap in cron's non-interactive shell: the binary run in cron is not the one you think — we come back to it below.

The shared hosting trap: only a numeric offset, no zone name#

The first thing anyone does is write in the database session SET time_zone = 'Europe/Berlin' and feel relieved. On many shared hosts this comes back with an error:

SET time_zone = 'Europe/Berlin';
-- ERROR 1298 (HY000): Unknown or incorrect time zone: 'Europe/Berlin'

The reason is that MySQL reads named zones from its own internal tables and those tables are empty by default. Filling them is the server administrator's job (loading the operating system's time zone data into the mysql database) and on shared hosting you have no such access. So the only thing you can set is a numeric offset:

SET time_zone = '+05:30';

This works, but it has a side effect you need to know: a numeric offset is only a number, not a rule. A named zone like Europe/Berlin carries the whole history of that region's rules; a numeric offset knows nothing. That means if daylight saving time changes, a named zone adjusts itself and a numeric offset does not — you have to change it by hand and until you do, everything is an hour wrong.

And that is where we reach the real answer. If the only offset you set in the database is +00:00, this problem does not exist at all; because the UTC offset never changes. The right solution is not to put both clocks on local time; it is to put both on UTC.

The cure: store in UTC, display in local time#

The rule is one line and the rest of this article is only the details of carrying out that line:

Store and compare everything in UTC, and convert to local time only at the last moment, in the display layer.

Why is this the only architecture that does not break? Because UTC is a fixed offset and no government decides anything about it. Whenever the official clock rules change — daylight saving returns, a country's offset shifts by half an hour, a new zone is defined — your stored data stays intact and correct and only the display layer needs updating. If you do the reverse and store local time, every rule change means your old data from then on lies by an hour and you have no way to detect it, because you did not write in the date column which rule it was stored under.

The second reason is more practical: with UTC, comparisons are unambiguous. Sorting works correctly, ranges work correctly, and no hour is repeated or vanishes (which really happens in local time at a daylight saving change, when an hour of the day either exists twice or does not exist at all).

The important thing in this diagram is the conversion point: one point, not several. If the conversion is spread across several places, sooner or later it gets converted twice somewhere and you get a double offset — which is even harder to diagnose than a single one.

Laravel settings: app.timezone and the database connection time zone#

Three settings are needed and all three must be done together. One of them alone is not enough.

First, the application's time zone:

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

That means every now(), every Carbon that models return, and every created_at that Laravel builds on insert is in UTC.

Second, the database connection's time zone. Laravel knows the timezone key for the MySQL driver and, if it is set, runs SET time_zone right after the connection opens:

// config/database.php
'mysql' => [
    'driver' => 'mysql',
    // …
    'timezone' => '+00:00',
],

We used a numeric offset and not UTC, for exactly the reason given above: +00:00 works on any server even if the time zone tables are not loaded.

If you are just setting the project up on shared hosting, do this setting right at the start along with the rest in the shared hosting checklist for Laravel; changing it after the tables are full costs much more.

Third, PHP's own clock in php.ini. If date.timezone is still set to a local zone, Laravel overrides it with app.timezone for its own code, but any independent library or script that runs outside Laravel's boot goes back to php.ini. To make it uniform, in cPanel's php.ini editor set:

date.timezone = UTC

A serious warning: if the project has already stored data in local time and you now switch app.timezone to UTC, the old data does not fix itself. It was written in local time and from now on will be read as UTC, so all of it is interpreted shifted by the offset. Before the switch you must write a migration that shifts the existing date columns, test it on a backup copy, and know which columns were really written by your code and which by the database's NOW() — because these two groups do not have the same offset and must not be shifted the same way.

A local calendar is a display layer, not a data type#

We write this separately because it does the most damage in projects for non-Gregorian calendars (Hijri, Jalali and others): calendar conversion is a display operation. A date in a local calendar is the same Gregorian instant written another way; it is not new data.

So never store a local-calendar string in a date column. If you ever see a varchar full of values like 1404/06/16, right there you have lost two things:

• Sorting and ranges. String comparison on a local-calendar date sometimes accidentally works and sometimes does not, and a date index no longer means anything. BETWEEN on a string is not what you think.
• Time. A local-calendar date as a string usually keeps only the day and throws away the time. Then when you need to know at what hour an order was placed, you no longer have it.

Plus a practical trap: varchar length in MySQL is counted in characters, not bytes, but the same varchar(10) that looks right-sized for "1404/06/16" overflows as soon as a time or an extra separator enters the string, and you run into error 1406 and the data too long message — an error whose message gives no hint about dates and takes time to trace.

The right pattern is simple: keep the datetime column in UTC, and in the display layer first convert to local time and then to the local calendar:

$local = $order->created_at->copy()->timezone('Asia/Kolkata');
// then give $local to the calendar conversion package

The order of these two matters. If you hand the UTC value directly to the calendar converter, orders recorded in the first hours after local midnight show up a day behind. That is another one of those confusing symptoms: a report where only a few early-morning records are wrong.

The syntax of calendar conversion differs depending on the package you use; what does not differ is that the conversion must be in the display layer and applied to a value that has already been converted to local time.

Queries that call NOW() and CURDATE() themselves#

Now back to that same coupon. The query that got us into trouble looked like this:

$coupon = Coupon::where('code', $code)
    ->whereRaw('expires_at > NOW()')
    ->first();

It looks harmless and in a correctly configured environment it works fine. The problem is that this query takes the time decision out of the application and hands it to the database. That means even if you set app.timezone to UTC, this line still reads from MySQL's clock and if that clock is elsewhere, you never notice.

The right pattern is to pass the time in from PHP:

$coupon = Coupon::where('code', $code)
    ->where('expires_at', '>', now())
    ->first();

Now now() is a Carbon in UTC, Laravel binds it as a parameter to the query, and the comparison is made between two values from the same reference. The database clock no longer takes part in the decision at all.

The same goes for CURDATE() and it is worse there, because CURDATE() decides the day boundary and the day boundary is exactly where a few hours of gap does the most harm. Do not take "today's orders" like this:

Order::whereRaw('DATE(created_at) = CURDATE()')->count();

Instead, build the day boundary in PHP in local time and then move it to UTC:

$start = Carbon::now('Asia/Kolkata')->startOfDay()->utc();
$end   = $start->copy()->addDay();

Order::whereBetween('created_at', [$start, $end])->count();

This version has another advantage: since no function is applied to the column itself, the created_at index is used. DATE(created_at) disables the index and slows the query on a large table.

Cron works with the system clock, not the application clock#

Here a third clock enters that usually nobody counts: the server operating system's clock, which cron uses to decide.

Cron knows nothing about your app.timezone. When you write in cPanel that a job should run at 0 0 * * *, it means the system's midnight. If the system is on UTC, that moment in your zone is another hour entirely. Now if that job is "yesterday's sales report", your report has pulled several hours of the current day into itself and left out the same amount from the start of yesterday.

Some versions of cron allow you to define a time zone at the top of the crontab, but that feature varies by version and is not something you can count on in cPanel. The dependable way is for cron to have one single job and for the scheduling decision to be made inside Laravel itself:

* * * * * /usr/local/bin/php /home/user/app/artisan schedule:run >> /dev/null 2>&1

And then in the schedule definition, write the time zone explicitly (where it is defined depends on the Laravel version — in newer versions it is the routes/console.php file):

Schedule::command('report:daily')
    ->dailyAt('00:15')
    ->timezone('Asia/Kolkata');

Now cron only fires every minute and the real schedules work with the clock you set yourself. If the PHP binary path in cron was not the one you see in the terminal, first read the alias trap in cPanel cron; otherwise your whole schedule may not run at all and you will think the problem is the time zone.

The timestamp versus datetime column trap#

These two column types look alike and behave completely differently with time zones:

That means TIMESTAMP converts by itself and DATETIME does not. If your session time_zone has a different value each time, the value you read from a TIMESTAMP column will differ too — even though the bytes on disk have not moved.

Three practical consequences:

• If you have explicitly locked the connection time zone to +00:00, both types behave the same and this difference does not bother you. That is one more reason to lock that setting.
• The worst case is having both TIMESTAMP and DATETIME in one table. Then two columns of one row are read under two different rules and the gap between them grows and shrinks depending on the session setting.
• TIMESTAMP has an allowed range that ends in 2038. For dates far in the future — say the validity of a long subscription — use DATETIME right now.

In our own tables we chose DATETIME, in UTC, with the connection time zone locked to +00:00. This combination has no hidden behaviour: what you write is what comes back.

Test: write a record and read it through both paths#

After any change to these settings, run a ten-second test. The logic of the test is simple: write a record, then read it both through the ORM and through raw SQL, and put the values side by side.

$id = DB::table('orders')->insertGetId([
    'user_id'    => 1,
    'total'      => 0,
    'created_at' => now(),
    'updated_at' => now(),
]);

$raw = DB::selectOne(
    'SELECT created_at, NOW() AS db_now, UTC_TIMESTAMP() AS db_utc FROM orders WHERE id = ?',
    [$id]
);

dump([
    'php_now'    => now()->toIso8601String(),
    'model'      => Order::find($id)->created_at->toIso8601String(),
    'raw_column' => $raw->created_at,
    'db_now'     => $raw->db_now,
    'db_utc'     => $raw->db_utc,
]);

The right output looks like this:

• db_now and db_utc must be equal. If they are not, the connection time zone is not yet on UTC.
• raw_column must be the same instant as php_now (without offset, since both are UTC).
• model must show the same instant with a zero offset (+00:00).

And finally a display test: look at the same record in the user interface. It should show the correct local time, that is, ahead of what is in raw_column by your zone's offset. If the interface shows the same UTC value, you missed the display layer conversion; if it is twice the offset ahead, you are converting twice.

Run this test once right after local midnight too. The day boundary is where all these bugs show themselves and if it is right there, the other hours are right too.

The main lesson#

A time zone is not a setting; it is a contract between all the components that work with time. PHP, MySQL, cron and the display layer — each has its own clock and none asks the others.

If we take one sentence to carry away: whenever you see a constant time gap, do not look for a logic bug; look for the second clock. The size of the gap gives the address itself. Your zone's offset from UTC means PHP and MySQL disagree. A one-hour gap means somewhere is still applying a different daylight saving rule. Double the offset means you converted twice.

And the solution is always the same: choose one time reference for storing and comparing — UTC — and push the conversion to local time and the local calendar to the last layer, where it is only printing something.