MariaDB convert_tz () (copying data by converting time zone on the fly)

Question

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

Answer 1

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';

Answer 2

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.