Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2013
    Posts
    0

    Unanswered: MySQL 5.5 UTC importing to MySQL 5.6 EST

    My source is MySQL 5.5 having incorp db with UTC time zone at Mysql Sqerver.

    Now I want to import this database to new schema called Flyzone with EST time zone. But this time zone should effect only to one database not to other existing databases.

    How can be done? any idea?

  2. #2
    Join Date
    Dec 2013
    Posts
    43
    Provided Answers: 1
    blackeagle99,

    I don't think this will accomplish what you need exactly, but it should assist
    you in developing a two-step process.

    BACKUP your data first !!!

    You can break this into two tasks.

    Get a list of all Table/Column pairs that represent date fields.

    Fine-tune your list of values for the DateTime data types:

    Code:
    Select a.Name, b.name
    From   Flyzone..Sysobjects as a inner join Flyzone..Syscolumns as b on
              a.id = b.id
    Where  b.type in (111)  -- You can research for "other" date values
    Then, you can generate a SQL command for each pair:

    Code:
    Select 'Update ' + a.Name + ' Set ' + b.name + ' = DateAdd(-5, h, ' + b.name + ');'
    From   Flyzone..Sysobjects as a inner join Flyzone..Syscolumns as b on
              a.id = b.id
    Where  b.type in (111)  -- You'll have an expanded list probably
    Then, you can paste the results of the above and execute them.

    This does not however take into account Daylight savings time. You can adjust
    this after the fact, or build a case statement into the above code.

    hth,
    Wayne

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •