{"id":3289,"date":"2012-06-24T09:34:30","date_gmt":"2012-06-24T13:34:30","guid":{"rendered":"http:\/\/richard.rathe.org\/?p=3289"},"modified":"2017-10-22T00:21:20","modified_gmt":"2017-10-22T05:21:20","slug":"timezone-solution-for-mysql","status":"publish","type":"post","link":"https:\/\/redmangrove.org\/rathe.org\/richard\/2012\/timezone-solution-for-mysql","title":{"rendered":"Timezone Solution for mySQL"},"content":{"rendered":"

I am new to SQL languages and I recently spent about two hours trying to bring some order to timestamps<\/strong> in my PTM project<\/a>. It was obvious that I should store universal UTC\/GMT<\/strong> date\/times in the database. It was not so clear how to convert these back to local time<\/strong>. Examples I found on the web were all commands typed at the mysql\u00a0prompt, not database queries. After quite a bit of poking around I finally stumbled on the AS Keyword<\/a> in mySQL. Seems to work well so I decided to share the solution here for the next newbie to learn from!<\/p>\n

\n
SELECT id, date, CONVERT_TZ(date,'+00:00','-04:00') AS corrected_date\r\nFROM `some_table`\r\nWHERE some_criteria = 'something'<\/pre>\n<\/blockquote>\n
This yields output that is nicely localized and completely abstracted from the actual data. Notice that the line for id=27 handles midnight properly.<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Output<\/th>\n<\/tr>\n
id<\/th>\ndate<\/th>\ncorrected_date<\/th>\n<\/tr>\n<\/thead>\n
1<\/td>\n2012-06-24 11:46:15<\/td>\n2012-06-24 07:46:15<\/td>\n<\/tr>\n
21<\/td>\n2012-04-23 18:22:21<\/td>\n2012-04-23 14:22:21<\/td>\n<\/tr>\n
28<\/td>\n2011-06-24 11:12:44<\/td>\n2011-06-24 07:12:44<\/td>\n<\/tr>\n
27<\/td>\n2012-04-23 01:53:51<\/td>\n2012-04-22 21:53:51<\/td>\n<\/tr>\n
29<\/td>\n0000-00-00 00:00:00<\/td>\nNULL<\/em><\/td>\n<\/tr>\n
30<\/td>\n2012-06-24 00:00:00<\/td>\n2012-06-23 20:00:00<\/td>\n<\/tr>\n
10<\/td>\n2012-06-12 17:12:37<\/td>\n2012-06-12 13:12:37<\/td>\n<\/tr>\n
31<\/td>\n2012-05-06 13:14:35<\/td>\n2012-05-06 09:14:35<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n","protected":false},"excerpt":{"rendered":"
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 … Continue reading “Timezone Solution for mySQL”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[409,32],"tags":[],"class_list":["post-3289","post","type-post","status-publish","format-standard","hentry","category-how-to","category-programming"],"_links":{"self":[{"href":"https:\/\/redmangrove.org\/rathe.org\/richard\/wp-json\/wp\/v2\/posts\/3289","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/redmangrove.org\/rathe.org\/richard\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/redmangrove.org\/rathe.org\/richard\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/redmangrove.org\/rathe.org\/richard\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/redmangrove.org\/rathe.org\/richard\/wp-json\/wp\/v2\/comments?post=3289"}],"version-history":[{"count":15,"href":"https:\/\/redmangrove.org\/rathe.org\/richard\/wp-json\/wp\/v2\/posts\/3289\/revisions"}],"predecessor-version":[{"id":7811,"href":"https:\/\/redmangrove.org\/rathe.org\/richard\/wp-json\/wp\/v2\/posts\/3289\/revisions\/7811"}],"wp:attachment":[{"href":"https:\/\/redmangrove.org\/rathe.org\/richard\/wp-json\/wp\/v2\/media?parent=3289"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/redmangrove.org\/rathe.org\/richard\/wp-json\/wp\/v2\/categories?post=3289"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/redmangrove.org\/rathe.org\/richard\/wp-json\/wp\/v2\/tags?post=3289"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}