How to deal with timezones between server and client?

Question

I'm developing a website where I have to deal with different possible timezones from the users. This becomes a great problem since the website hosts time-delicate events like auctions.

All dates/times on the server are in UTC. Database stores everything in UTC timestamps. PHP default timezone is set to UTC too (date_default_timezone_set('UTC');).

Now, my problem is how I should interact with the users, whether I'm only showing a date or, more important, I'm reading a date/time from user input.

A concrete example:

• An auction has a deadline, which I store in database as UTC.
• When I view the auction on the website, a javascript timer uses a Date object to calculate the remaining time. It automatically converts the timezone to GMT+0100 (my local timezone). So if the deadline is '2013-08-08 10:46:08' (UTC), the javascript date object will return Aug 08 2013 11:26:15 GMT+0100 (GMT Standard Time).
• If the current time is greater than 11:46:08 then the timer says that the remaining time is 00:00 (which is correct).

• But if I try to insert a bid, the server accepts since the condition on the MySQL INSERT evaluates to true:

  INSERT INTO Bids ... WHERE ... AND auction_deadline > NOW() ...

( because auction_deadline = '2013-08-08 10:46:08' and NOW() = '2013-08-08 10:26:50')

All this mumbo jumbo of timezone melts my brains. What am I missing here? I'm almost certain that storing all dates/times in UTC inside the database is the best. I just can't think crystal clear how do deal with it between the user and the database.

Answer 1

Your problem doesn't involve timezones at all, just the fact that clients can turn their clocks or have their clock skewed considerably. For that the fix is to poll the server every once in a while for an offset fix to use in calculations.

In fact, you don't even need date objects. There is a certain universal instant in time when the auction ends. Let's say it is 1375960662823. Right now, the universal instant in time is 1375960669199, so from that we see that the auction ends in 6 seconds (1375960662823 - 1375960669199 ~ 6000 ). It will end in 6 seconds regardless if I am in Morocco or Japan. Do you understand it yet?

To generate these numbers, on the client side you can call var now = Date.now() + skewFix where skewFix is the correction that needs to applied in case client has time skew or manually set their computer to wrong time.

In PHP, you can generate it with $now = time() * 1000;