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

问题描述：

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.

我的遗留数据库中有表以CST格式存储日期。现在我想将它们存储在UTC中。 p>

我想在mysql中创建一个脚本/查询，它将在我的数据库中搜索带有datetime code>字段的表，然后将那里的值转换为UTC。 p>

无需编写表名即可执行此操作的脚本非常有趣。 p>


 
 我有查询转换时间  CONVERT_TZ  code>的区域，对我来说不是问题。 但我想要一个脚本，在
数据库中搜索以datetime为数据类型的表和字段，然后在这些字段上运行脚本。 p> 
  blockquote> 
 
 
 知道我怎么能这样做吗？ 任何引用都会很好，不必是代码。 p> 
  div>

答

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' )");
    }
}

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

相关推荐