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.
| Output | ||
|---|---|---|
| id | date | corrected_date |
| 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 |
| 29 | 0000-00-00 00:00:00 | NULL |
| 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 |