DateTime values should always be stored in UTC
Developers are sometimes confused about this issue but the fact of the matter is that you should always store Datetime values in your database in UTC and almost never in a local time zone.
Canyon De Chelly, Az/NM border: One of the many places where naive use of local time will cause you problems!
photo credit: JBColorado
You should use UTC because:-
1) There are no ambiguous or missing times in UTC time. Any timezone with daylight savings time has a spring-forward gap and a fall-back repeated hour. That messes up all manner of calculations: How many hours is it from 2:05AM to 2:07AM? In UTC time it's always 2 minutes but in local time it could be 2 minutes or it could be 1 hour and 2 minutes or it could be 'error no such time'.
2) All your times sort correctly even if they were entered by users in different timezones. So, user A commenting on user's B comment on user C's photo doesn't have their comments listed in the wrong order even if user C is in Europe, user A is in Japan and user B is in the USA. And user D gets to see the whole thread in whatever timezone they are in because you convert UTC to local on the way out in the user's local timezone.
3) Daylight savings time rules change from time-to-time. To be able to reliably say how many hours it is from DateTime A to DateTime B you would need to store every historical variation in the DST rules in order to be able to calculate that correctly, and even then because of (1) you couldn't calculate it in every case because there would be ambiguities.
4) Worse even that (3) is the fact that the boundaries of timezones are not constant but have changed over time. So your database of historical timezone information also needs to include accurate geographical information for every time zone AND you need to record the location of every local datetime value in order to be able to calculate when it happened in UTC time (and even then (1) causes problems).
5) If you think you understand timezones completely and can deal with all these issues go read up on Navajo Indian Reservations, fractional time zones and then consider what happens if you are recording datetime values associated with a moving vehicle.
Storing datetime values in UTC is the only sensible answer to all of these issues.
One of very few exceptions to this rule is when you have a local datetime that you cannot convert to UTC time because you can't be sure of the timezone, e.g. Datetimes from digital cameras or camcorders where all you have is a local time and have no idea what timezone it is in. In such cases you can store a local datetime but you really have very little ability to do much with it apart from redisplay it.
The other one to watch for is where you have an event at a given time in a given location, e.g. 10AM: Family Reunion in Taos, New Mexico. In this case you need to do the UTC conversion in some timezone other than the one your user is in right now. And you want to store the fact that it is a time that should be shown in the local New Mexico timezone not the user's timezone.
Grouping is another key issue - you need to convert your UTC times into local times BEFORE you group them into days because my 'today' might not be the same as your 'today'. This complicates your ability to perform grouping in your database.
UTC is the only sensible format to store date time information in a database. If you can't generate a UTC datetime from the input then the value you have may as well be stored as a string because there's very little you can do with it without a lot of extra effort!
