The sale that started at the wrong hour

A retailer scheduled a flash sale for 9 a.m. The shop software was set to the store's local time. The server's clock, which a plugin used to trigger the price changes, was in UTC.

The new prices appeared three hours early, in the middle of the night. A few hundred bargain hunters found out and bought heavily before the retailer woke up. The discount was meant for the launch crowd, and the stock was gone by breakfast.

They fixed the time zone settings and added a test where a scheduled change is verified on a staging site at the exact moment it should happen. The retailer here, a seller of outdoor clothing, is a composite and the figures are illustrative. The mechanism, though, is one of the most common ways for scheduled things to go wrong: two systems each believing they know what "nine o'clock" means.

Four clocks in one shop

When people say "the server's time", they usually mean one clock. In practice, a web shop has at least four, and they do not have to agree.

• The operating system clock of the server, which is almost always kept in UTC on hosting platforms, whatever the customer's location.
• The database, which has its own time zone setting that may follow the operating system or may not.
• The programming language runtime, such as PHP, with a default time zone set in its configuration.
• The application, which often has a store time zone chosen by the owner in a settings screen.

A scheduled price change touches all four. Someone types "9:00" into a form in the application, which stores the text in the database. Later a timer on the server wakes up and compares the stored text with the current time. Whether the comparison is right depends on whether both sides were speaking the same time zone when they wrote and read the value.

In this shop, the store's local time was three hours behind UTC. The owner entered 09:00 into the sale form, meaning nine in the morning where the customers live. The plugin that applied the change stored that as a plain string, "09:00", without a zone. When its timer compared it with the server's UTC clock, it fired when UTC reached 09:00, which was 06:00 on the shop's wall clocks.

What the night looked like

At 06:00 local time the new prices went live. Nobody at the shop was awake; the team had planned to be at their desks from eight, with the launch email scheduled for nine. The first sign came from the outside: a deal-hunting forum picked up the lower prices and posted the link within the first hour.

Orders began arriving at a rate the shop normally saw on a good afternoon. By seven, several hundred items had gone, concentrated on the three most heavily discounted jackets. The stock count of those lines fell to zero before the first person at the retailer had opened a laptop.

The payment provider's notifications were the loudest sign. The owner woke to a phone full of order confirmations, assumed it was a very good morning, and only on logging in realised the sale should not have started. Meanwhile the ordinary customers who had been told "9 a.m." arrived at nine to find their favourite items sold out, many of them to people who had not been on the mailing list at all.

Wrong turns on the way to the cause

The first reaction was to suspect an attack. The speed of the buying, the spike in orders and the unfamiliar names made it look like a bot or a leak of the discount code. The team spent an hour looking through access logs for automated behaviour. They found plenty of traffic, but all of it ordinary: people following a link and buying.

The second theory blamed the plugin, with the reasoning that it must have "run early". That was nearer the truth but not helpful, because the plugin had no setting for time zone at all, and the vendor's documentation said only that the sale started "at the chosen time".

What settled it was the database. The scheduled change was stored as a row with a start time and no zone. Comparing that row with the clocks made the three-hour offset obvious, and a quick look at the other scheduled items in the shop showed the same pattern: every one of them fired three hours before its stated time. They had never noticed because earlier events were created for early-morning posts that nobody watched.

The repair

Stopping the sale came first: the discount was cancelled, the prices restored and the orders reviewed. The retailer decided to honour the orders already taken, on the grounds that the prices had been published by its own system, and wrote to the customers who had missed out on the stock with a smaller apology offer. The cost was real, though the retailer preferred to treat it as the price of a lesson.

The technical fix was in three parts.

1. Choose one time zone for storing times. Every timestamp the shop writes should be in UTC, with the conversion to local time happening only when the value is shown to a person.
2. Align the settings, so the operating system, database, PHP and the application agree about what they are using, and note where each setting lives.
3. Replace the plugin with one that stores a full date, time and zone, or at least documents what it assumes.

The checks that confirm the clocks are quick, and worth running on any server you are responsible for:

date; date -u
timedatectl | grep -i "time zone"
php -r 'echo date_default_timezone_get(), "\n";'
mysql -e "SELECT NOW(), UTC_TIMESTAMP(), @@global.time_zone, @@session.time_zone;"

If NOW() and UTC_TIMESTAMP() differ, the database is converting somewhere, and anything that stores timestamps through it deserves a second look.

Daylight saving makes it worse

This shop's offset was a constant three hours, which made the error easy to read. Many regions change their offset twice a year, and they change on different dates. A shop based in one place with customers in another can have the gap between its clock and UTC shift by an hour, and the gap between it and its customers shift on yet another day.

A sale planned for the week of a clock change is especially risky. A scheduled item can fire an hour early or late purely because of the date. Stored in UTC, the moment is fixed and unambiguous; only the display changes. Stored as local time, the same string can mean two different instants (when clocks go back, one hour occurs twice) or none at all (when they go forward, one hour does not exist).

Try it on your own site

Pick any scheduled event in your own system, a post, a price change or an email campaign, and set it for five minutes ahead on a staging copy. Then watch.

1. Note the time in your application when you save it, and the exact time you expect it to fire.
2. Check that the clocks line up using the commands above, on the staging server itself.
3. Wait. Confirm the event happened at the moment you wrote down, not an hour or more either way.
4. Repeat with a time just after midnight, since date rollovers hide extra errors.

Also look at how the database stores the value. A column of type DATETIME has no zone; TIMESTAMP converts using the session setting; a text field is a trap. The glossary defines the terms.

What would have caught it

• Know which time zone each system uses: server, database, application and plugins.
• Test scheduled events with a near-future time before using the real one.
• Prefer storing times in UTC and converting for display.
• Read what a plugin stores in the database, in particular whether a zone is saved with the time.
• Hold back the stock-sensitive items until the sale has demonstrably started on schedule, or put a purchase limit in place for the first hour.