Open nxhai98 opened 7 months ago
Hi @nxhai98,
Thanks for your concern. Can you please elaborate more on what is the issue with mapping MySQL's TIMESTAMP with PostgreSQL's timestamp?
I have tested below simple test case -
-- mysql table:
CREATE TABLE mysql_test_timestamp(a int primary key, b timestamp);
-- postgres foreign table:
CREATE FOREIGN TABLE f_mysql_test_timestamp(a int, b timestamp) SERVER mysql_svr OPTIONS (dbname 'suraj', table_name 'mysql_test_timestamp');
-- Insert timestamp value and verify it.
edb@117019=#insert into f_mysql_test_timestamp values (1, '2020-01-01 00:00:00');
INSERT 0 1
edb@117019=#select * from f_mysql_test_timestamp;
a | b
---+--------------------
1 | 01-JAN-20 00:00:00
(1 row)
-- verify values on MySQL
mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| SYSTEM | SYSTEM |
+--------------------+---------------------+
1 row in set (0.00 sec)
mysql> select * from mysql_test_timestamp;
+---+---------------------+
| a | b |
+---+---------------------+
| 1 | 2020-01-01 00:00:00 |
+---+---------------------+
1 row in set (0.00 sec)
mysql> set session time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)
mysql> select * from mysql_test_timestamp;
+---+---------------------+
| a | b |
+---+---------------------+
| 1 | 2019-12-31 18:30:00 |
+---+---------------------+
1 row in set (0.00 sec)
I can see the same value in Postgres as well as MySQL side. If we change session timezone in MySQL then that value changes accordingly and this is what is mentioned in MySQL docs -
MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval.
Also, do you think, we should set session time_zone to '+00:00' after establishing a connection with MySQL server to store date value in UTC timezone if timezone difference between MySQL and Postgres?
Hi @surajkharage19,
Can you please elaborate more on what is the issue with mapping MySQL's TIMESTAMP with PostgreSQL's timestamp?
I am concerned about the user not being able to know the value inserted by the mysql_fdw store in the MySQL server because user may not know time zone of mysql_fdw connection.
I think MySQL's TIMESTAMP with PostgreSQL's timestamp is not correct.
timestamp
just a time presentation, it has no meaning if user use it in multiple time zone system I have tested below simple test case I can see the same value in Postgres as well as MySQL side.
In you example, there is a confused data:
2020-01-01 00:00:00
is Postgres timestamp and stored value in PostgreSQL same with 2020-01-01 00:00:00 UTC
2020-01-01 00:00:00
stored as 2019-12-31 18:30:00 UTC
in MySQLAlso, do you think, we should set session time_zone to '+00:00' after establishing a connection with MySQL server to store date value in UTC timezone if timezone difference between MySQL and Postgres?
Yes, I think so. It's also important to consider PostgreSQL's time zone when selecting data.
By this way, MySQL and Postgres will store same epoch value and does not depend on MySQL server time zone
Thanks for your clarification.
I think, apart from mapping MySQL timestamp with Postgres timestamptz, we need to set session time_zone after establishing a connection with MySQL. However, we will do more study on this before fixing this.
Hi @nxhai98,
We have committed the changes for the timestamp data type as reported by you. Please check on this.
Hi team,
Currently, at
IMPORT FOREIGN SCHEMA
,mysql_fdw
map MySQL'sTIMESTAMP
with PostgreSQL'stimestamp
. I think it does not a correct behavior, based on: https://dev.mysql.com/doc/refman/8.0/en/datetime.html=> PostgreSQL's
timestamptz
should be the nearest mapped type.How do you think?
When map MySQL's
TIMESTAMP
with PostgreSQL'stimestamptz
, I found an issue:As you can see, timestamp value not same as value inserted by foreign table. Root cause:
when insert, mysql_fdw adjust timestamptz value to UTC time zone: https://github.com/EnterpriseDB/mysql_fdw/blob/9204d9ebb83ae21f63f032de74e92a001e1ffa9f/mysql_query.c#L352
However, session time zone of mysql connection is not UTC/GMT and inserted value treated as in session time zone timestamp.
when select, mysql connection and postgres timezone may not the same and MySQL's TIMESTAMP literal have no time zone information => the PostgreSQL input function (cast) not work well.