How to deal with timezones between server and client?
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.
No comments:
Post a Comment