I am new to SQL languages and I recently spent about two hours trying to bring some order to timestamps in my PTM project. It was obvious that I should store universal UTC/GMT date/times in the database. It was not so clear how to convert these back to local time. Examples I found on the web were all commands typed at the mysql prompt, not database queries. After quite a bit of poking around I finally stumbled on the AS Keyword in mySQL. Seems to work well so I decided to share the solution here for the next newbie to learn from!
SELECT id, date, CONVERT_TZ(date,'+00:00','-04:00') AS corrected_date FROM `some_table` WHERE some_criteria = 'something'
This yields output that is nicely localized and completely abstracted from the actual data. Notice that the line for id=27 handles midnight properly.
|1||2012-06-24 11:46:15||2012-06-24 07:46:15|
|21||2012-04-23 18:22:21||2012-04-23 14:22:21|
|28||2011-06-24 11:12:44||2011-06-24 07:12:44|
|27||2012-04-23 01:53:51||2012-04-22 21:53:51|
|30||2012-06-24 00:00:00||2012-06-23 20:00:00|
|10||2012-06-12 17:12:37||2012-06-12 13:12:37|
|31||2012-05-06 13:14:35||2012-05-06 09:14:35|