php - Mysql search tables find datetime fields and change timezone

337

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 withdatetime 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 withCONVERT_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.

496

Answer

Solution:

Fetch the columns first, traverse the array to decide if it matches theType 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' )");
    }
}

People are also looking for solutions to the problem: Trying to retrieve tables only with particular column php mysql

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.