Why does my scheduled job run hours late or not at all?

Almost always because the value was written using one clock and read using another. If the application writes local wall clock time and the worker compares it against a UTC clock, the job becomes due when the UTC clock catches up, which is the offset in hours later. If the worker only picks up jobs that became due in the last few minutes, the row is skipped entirely and sits there with its original status.

Should I store dates in UTC or in local time?

Store the instant in UTC and convert when you display it. Also store the zone the user chose, as a zone name and not an offset, if you ever need to recompute or re-render the value. That way the stored instant never changes meaning and the local rendering can follow whatever rules apply on that date.

What is the difference between a naive datetime column and a timestamp column?

A naive datetime column stores wall clock digits with no offset attached, so it means whatever the reader assumes. A timestamp column stores an instant and converts on the way in and out using the session time zone, so the same row can read differently from two connections. Mixing both in one schema gives you two columns that look identical and behave differently.

How do I handle daylight saving in a recurring schedule?

Store the wall clock time and the zone name for anything that repeats, and compute the next instant at run time instead of storing a fixed offset. A recurring job at 09:00 in a zone with daylight saving is a different instant in January and in July. Also decide what happens on the two broken days each year, when a wall clock time either does not exist or happens twice.

Data Operations

The campaign that never sent: time zones in the database

A job scheduled for 09:00 sits in the queue with no error at all. The application wrote local time, the worker reads UTC, and the offset does the rest.

By Ömer YanbaşPublished April 18, 20255 min read

A campaign is scheduled for 09:00. At 09:00 nothing happens. The worker is alive and picking up other work, the row is still sitting there with its original status, and there is no error in any log. The value in the database was written from the application server's local clock, and the query that decides what is due compares it against a UTC clock. The two are three hours apart, so the job is not due yet, and in the worst version of this bug it never becomes due at all.

What actually happens

Three clocks are involved and each one can be set differently: the host clock of the application server, the session clock of the database connection, and whatever the scheduler decides to compare against.

The application takes 09:00 from a form, formats it as a plain string with no offset, and stores it. A string like 2025-04-18 09:00:00 carries no information about which clock produced it. It means whatever the next reader assumes it means.

The worker then asks a reasonable looking question:

SELECT id FROM campaigns
WHERE status = 'SCHEDULED' AND send_at <= UTC_TIMESTAMP()
ORDER BY send_at LIMIT 50;

At 09:00 local time in a zone three hours ahead of UTC, the UTC clock says 06:00. The row is not due. It becomes due at 12:00 local, three hours late, and the campaign goes out in the middle of the day with a good morning subject line.

That is the benign version. Two variants are worse.

The first is the opposite direction. The application writes UTC and the worker compares against the local clock, so everything fires three hours early, and anything scheduled for later today fires immediately.

The second is the one that never sends. Plenty of schedulers do not ask for everything that is due, they ask for everything that became due in a short window, to avoid picking up a backlog after an outage:

WHERE send_at BETWEEN UTC_TIMESTAMP() - INTERVAL 5 MINUTE AND UTC_TIMESTAMP()

A row that is three hours off never falls inside that window while it still has the right status. It sits in the queue for as long as the table exists, and nobody gets an error, because nothing failed. The job that was supposed to run simply was not selected.

Column types add a second layer. A naive datetime column stores digits and attaches no meaning to them. A timestamp column stores an instant and converts it on the way in and on the way out using the session time zone, which means two connections with different session settings read different values out of the same row. When a schema has both, two columns that look identical on screen behave differently under the same query.

How to see it

One query settles it. Put the stored value, both clocks and the session settings side by side:

SELECT id,
       send_at,
       NOW()                                        AS db_local_now,
       UTC_TIMESTAMP()                              AS db_utc_now,
       @@session.time_zone                          AS session_tz,
       @@global.time_zone                           AS global_tz,
       TIMESTAMPDIFF(MINUTE, send_at, UTC_TIMESTAMP()) AS minutes_past_due
FROM campaigns
WHERE id = 4721;

send_at              2025-04-18 09:00:00
db_local_now         2025-04-18 09:02:11
db_utc_now           2025-04-18 06:02:11
session_tz           SYSTEM
global_tz            SYSTEM
minutes_past_due     -178

The value is due according to one clock and not due according to the other, and minutes_past_due is negative while the wall clock in the office says it is past nine. That is the whole bug on one screen.

Then compare with the application host:

date +"%F %T %Z"; date -u +"%F %T"
# 2025-04-18 09:02:14 +03
# 2025-04-18 06:02:14

If the host prints a local zone and the worker query uses a UTC function, you have found the pair that disagrees. If the database session says SYSTEM, the answer depends on a setting on a machine, which is the part that changes without a deploy.

The fix

Store the instant in UTC, convert once on the way in, and format at the edge. In practice that is four changes.

Convert at the boundary where the user's intent is still known. The form knows the zone, the database does not:

// wrong: a string with no offset means whatever the next reader assumes
const sendAt = '2025-04-18 09:00:00';

// right: convert once, at the edge, with the zone the user actually chose
const sendAt = toUtcInstant('2025-04-18 09:00', 'Europe/Istanbul');
// 2025-04-18T06:00:00Z

Pin the session so the behaviour does not depend on the host. Run this on every connection, from the pool setup, not from a script somebody remembers to run:

SET time_zone = '+00:00';

Keep the user's zone in its own column. One column for the instant, one for the zone name, and for recurring schedules one for the wall clock time the user typed. A one off send only needs the instant. Anything that repeats needs the wall clock and the zone, so the next occurrence can be computed under whatever rules apply on that date.

Compare like with like in the worker. If the column holds UTC, the comparison uses the UTC function, and there is no place left in the code where NOW() and UTC_TIMESTAMP() are used against the same column.

If you already have wrong rows, fix them explicitly rather than by adding an offset in the application:

UPDATE campaigns
SET send_at = CONVERT_TZ(send_at, '+03:00', '+00:00')
WHERE status = 'SCHEDULED' AND send_at >= '2025-04-01 00:00:00';

Do that once, with the range written down, and never as a permanent correction inside the read path. A correction in the read path is how a system ends up with two conventions and no way to tell which row follows which.

How to check it worked

Ask the database to show the same row three ways and schedule one job two minutes out:

SELECT send_at                                   AS utc_instant,
       CONVERT_TZ(send_at, '+00:00', '+03:00')   AS shown_to_user,
       TIMESTAMPDIFF(SECOND, UTC_TIMESTAMP(), send_at) AS seconds_until_due
FROM campaigns WHERE id = 4722;

utc_instant        2025-04-18 06:00:00
shown_to_user      2025-04-18 09:00:00
seconds_until_due  118

The stored value and the value on screen differ by the offset, which is what you want, and the countdown agrees with a watch. Then let the job run and check that it started within a few seconds of the target, not within a few hours.

What to watch out for

An offset is not a zone. Storing plus two hours for a zone that observes daylight saving is correct for half the year. Store zone names, and let the zone database resolve the offset for the date in question.
Zone rules change by law. One country I work in moved to a permanent offset in 2016 and stopped changing clocks, which broke every hard coded summer rule and every machine with an old zone database. Update the zone data with the rest of the system, and treat an out of date zone file as a production bug.
A server move, a container rebuild or a base image update can change the host zone with nothing in your repository changing. Anything that depends on the host clock changes meaning silently, which is the same class of surprise as a process manager that does not bring the app back after a reboot.
The two broken days a year are real. On the spring transition a wall clock time can be skipped entirely, and on the autumn one it happens twice. Decide in advance whether a skipped job runs at the next valid minute or not at all, and make repeated runs harmless.
A campaign that never leaves the queue and a campaign that goes out on time and lands in spam because the From domain does not align look identical on a dashboard that only counts sends. Track scheduled, started and delivered separately, or you will fix the wrong thing.

Time zone bugs are quiet because nothing fails. Every component does exactly what it was told, the row keeps its status, the logs stay clean, and the only evidence is that a thing which should have happened did not. The habit that prevents it is boring and cheap: one clock in storage, one conversion at each edge, the zone kept next to the value, and a query you can run in ten seconds that shows both clocks next to the row. When a schedule misbehaves, run that query before reading a single line of the worker.

Questions and answers

Why does my scheduled job run hours late or not at all?: Almost always because the value was written using one clock and read using another. If the application writes local wall clock time and the worker compares it against a UTC clock, the job becomes due when the UTC clock catches up, which is the offset in hours later. If the worker only picks up jobs that became due in the last few minutes, the row is skipped entirely and sits there with its original status.
Should I store dates in UTC or in local time?: Store the instant in UTC and convert when you display it. Also store the zone the user chose, as a zone name and not an offset, if you ever need to recompute or re-render the value. That way the stored instant never changes meaning and the local rendering can follow whatever rules apply on that date.
What is the difference between a naive datetime column and a timestamp column?: A naive datetime column stores wall clock digits with no offset attached, so it means whatever the reader assumes. A timestamp column stores an instant and converts on the way in and out using the session time zone, so the same row can read differently from two connections. Mixing both in one schema gives you two columns that look identical and behave differently.
How do I handle daylight saving in a recurring schedule?: Store the wall clock time and the zone name for anything that repeats, and compute the next instant at run time instead of storing a fixed offset. A recurring job at 09:00 in a zone with daylight saving is a different instant in January and in July. Also decide what happens on the two broken days each year, when a wall clock time either does not exist or happens twice.