MySQL: keep server timezone or user timezone?

Question

I'm thinking about to store dates on user login in my site, but I don't know what is the most logical solution.

Initially, I though to use server timezone, and then to manage it using difference operations between server machine date and user machine date, but I've also considered to change it direclty using timezone and php class date, so:

<?php
// my server has for example America/New_York timezone
$user_timezone = "Europe/Rome";
date_default_timezone_set ($user_timezone);
$date = date ('Y-m-d H:i:s');
$sql = "UPDATE users SET user_last_modify = '$date', user_timezone = '$user_timezone' WHERE user_id = 'some id' LIMIT 1;";
$res = mysql_query ($sql);
?>

My ask is, what is the best solution, keep server timezone or use user timezone?
and if I use user timezone, should I save the timezone name too as in my example?

Answer 1

I'd recommend using server timezone or UTC, rather than storing different data for each user. This way, your database will be fully consistent, and you can perform some operations like comparisons without having to, for each entry, fetch the user_timezone column and perform the conversion (which is not fully free)

Answer 2

Use UTC. It will save you many hours of frustration.

User local time is a matter of presentation - it's much easier to convert to/from local time for a given user, than it is to track the TZ of each record's date fields in the database.

Even if using server's timezone may be tempting, DST rules may change on very short notice (see: Argentina DST 2009 - the govt has made the decision not to use DST, appx. 1 week before it was supposed to happen); in some cases, the timezone itself may change (see Time in Indiana ). UTC's definition is unlikely to undergo any such drastic change.

(A story about server time and local time: had a server on U.S. West coast, moved it to U.S. East coast; apps were using server time; all hell broke loose. With virtualization, it's possible to easily and quickly move servers to different continents.)