Mysql搜索表查找datetime字段并更改时区

Question

I have tables in my legacy database that stored dates in CST format. Now I want to store them in UTC.

I want to create a script/query, in mysql, that would search my database for tables with datetime fields and then convert the values there to UTC.

A script that would do this without having to write table names would be fun.

I have the query to convert time zone with CONVERT_TZ, that is not a problem with me. But I want a script that searches through the database for tables and fields that has datetime as data type and then run the script on those fields.

Any idea how I can do that? Any reference would be nice, doesn't have to be code.

Answer 1

Fetch the columns first, traverse the array to decide if it matches the Type you want.

Then make the update is just fine.

<?php
$db = new Mysqli("host", "user", "pass", "db");

$column_query = $db->query("SHOW COLUMNS IN table");
if ($column_query->num_rows != 0){
    while ($column = $column_query->fetch_object()){
        if ($column->Type != 'datetime')
            continue;
        $db->query("UPDATE table SET {$column->Field}=CONVERT_TZ({$column->Field}, 'timezoneFrom', 'timezoneTo' )");
    }
}