-1

We are trying to copy data from database1 (DB1) to database2 (DB2) in runtime. DB1 and DB2 are on two different timezones lets say tz1 and tz2. We wish to convert DB1 Time column to tz2 while copying on the fly to DB2. We tried to use combination of convert_tz() and subquery but couldn't make it work. Any pointers in the right direction will be appreciated.

MariaDB version is 10.3.7

Akshay
  • 1
  • 2
  • Instead of simply voting it down, would appreciate any feedback on it. I am new to SQL and as mentioned I have tried combination of queries, I am simply asking for useful pointers not the exact query. – Akshay Feb 05 '20 at 15:26
  • Hi Akshay, and welcome! A downvote is warranted because "... couldn't make it work" isn't enough detail to understand how to help you. Please read [*How do I ask a good question?*](https://stackoverflow.com/help/how-to-ask) and [*I downvoted because "it's not working" is not helpful*](https://idownvotedbecau.se/itsnotworking/). There are also lots of posts on Meta about downvoting, such as [*When is it justifiable to downvote a question?*](https://meta.stackoverflow.com/q/252677). Please either edit your question, or delete it and ask a new one. Thanks. – Matt Johnson-Pint Feb 05 '20 at 16:22

2 Answers2

0

Thanks all for responding and voting. I have now managed to solve the issue in hand. Below query solved it (not the exact one for obvious reasons):

insert into DB2.tb2 select C1,convert_tz(T1,'UTC','ASIA/SHANGHAI'), C2, C3 from DB1.tb1 where C1 = 'xxx';

Akshay
  • 1
  • 2
0

Instead of ever touching timezone stuff, store into TIMESTAMP columns and make sure that clients know what timezone they are in. That way, a stored timestamp is automatically adjusted according to the reader's location.

Rick James
  • 135,179
  • 13
  • 127
  • 222