Skip to content

Instantly share code, notes, and snippets.

@esycat
Last active August 29, 2015 14:05
Show Gist options
  • Save esycat/3bc423b9c9568f82c69e to your computer and use it in GitHub Desktop.
Save esycat/3bc423b9c9568f82c69e to your computer and use it in GitHub Desktop.
For Tim & Marley with Love :-)
SELECT
DATABASE(), table_name, column_name, data_type,
CONCAT(
"UPDATE ", table_name,
" SET ", column_name, " = CONVERT_TZ(", column_name, ", 'UTC', 'Australia/Sydney')",
" WHERE ", column_name, " > '2014-08-05 22:00:00';"
) AS query
FROM information_schema.columns
WHERE
table_schema = DATABASE()
AND data_type IN ('datetime')
AND NOT (
(table_name = 'reviewlog' AND column_name = 'reviewDate') OR
(table_name = 'incidentlog' AND column_name = 'incidentDate') OR
(table_name = 'externalSurvey_log' AND column_name = 'reviewDate') OR
(table_name = 'notice' AND column_name = 'expiryDate') OR
(table_name = 'applicationsinterviews' AND column_name = 'interviewDateTime')
)
ORDER BY table_name, column_name
;
SELECT
DATABASE(), table_name, column_name, data_type,
CONCAT(
"UPDATE ", table_name,
" SET ", column_name, " = CONVERT_TZ(", column_name, ", 'Australia/Sydney', 'UTC')",
" WHERE ", column_name, " > '2014-08-05 22:00:00';"
) AS query
FROM information_schema.columns
WHERE
table_schema = DATABASE()
AND data_type IN ('timestamp')
ORDER BY table_name, column_name
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment