Working with timezones when storing dates in mysql

Question

I have just a basic site with registration. With all other data, it stores the registration date in MySQL. If I'm in USA or in Canada, and I register at 2017-03-07 (Y-d-m), in MySQL it's 2017-04-07 because we have 10 hours time difference. So if I want to display the date in my site, it says that it's happening in future. What do I have to do?

Answer 1

I think you have to dig a little deeper than just setting a default UTC_DATE. A UTC_DATE default could help solve the problem, but doesn't quite address the larger issue of working with time zones in applications.

There's three ways time zones are usually handled:

1. They're not. Assume that all users are on the same time zone as the server. In most cases, this is a terrible approach. The problem with the time zone approach that you choose is that it's near impossible to change after the fact, and there's a good chance that someone starting like this then needs to change it after. Besides which, if the server time zone changes (inadvertently or otherwise), this could serious side effects, to the point where your entire system can come crashing down.
2. User specifies time-zone for their account, Store UTC. In this case, the user, once creating a profile, selects the time zone they want to use for their account. This setting is stored in the database with that user account. Meanwhile, all dates stored in the database are stored using a standard time zone (naturally, this is UTC). Now, before storing any date that comes from the user, factor in the user's time zone and offset accordingly (to ensure that the date is stored in UTC). Before displaying any date, use their time zone offset to adjust on the presentation layer. This approach is significantly better than #1, but still has a few drawbacks. It's worth noting that the drawbacks with this one are not likely to be of the type that might render your application unusable, as is the case with #1.
3. Use Client/Agent Time Zones, Store UTC Any modern web client should be able to present it's current time zone to the server. Plan to store all server-side dates in a standard time zone (UTC), and plan that any dates passed to the server will have been offset to UTC by the client. This completely releases the server from caring about any time zones (really handy), but places a higher burden on your front end to ensure that it's converting everything to UTC from Local (sometimes a pain, but it really is the client's responsibility anyway...). Likewise, the server should serve only UTC dates, and the client should convert those to local time for display purposes. In my point of view, this is the only correct way to handle time zones.

The important thing to remember is that once you choose an approach, changing it later is extremely difficult! Choose wisely :)

Answer 2

For anyone who has a similar issue in the future, please refer to this very thorough discussion of time in programming. It helped me a lot.

One of the ways to handle time is to

store:

1. Get local time using a JavaScript library or any other front end.

2. Convert it to UTC Unix timestamp in JS front end.

3. Pass it to back end as UTC and store in the database as UTC.

and retrieve:

1. Get it from database as UTC,

2. Pass it through back end to JavaScript as UTC,

3. Have JS (front end) convert it to local time.

In summary, always handle time as UTC in the backend and database. Have front end convert time to local because only the front end can guess what timezone the user is, and it guesses pretty well most of the time. Although due to complexities of timezones, DSTs, traveling users, VPNs, etc. there doesn't appear to be a 100% fail proof solution.

Answer 3

Simply create your table to store UTC_DATE as a DEFAULT value.

CREATE MyTable
...
  MyColumn DATE DEFAULT UTC_DATE
...