MySQL's TIMESTAMP support

[nxhai98]

Hi team,

Currently, at IMPORT FOREIGN SCHEMA, mysql_fdw map MySQL's TIMESTAMP with PostgreSQL's timestamp. I think it does not a correct behavior, based on: https://dev.mysql.com/doc/refman/8.0/en/datetime.html

• MySQL's TIMESTAMP type range '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC
• MySQL's TIMESTAMP has time zone information and value affected by connection session time zone or global time zone of MySQL server

=> PostgreSQL's timestamptz should be the nearest mapped type.

How do you think?

When map MySQL's TIMESTAMP with PostgreSQL's timestamptz, I found an issue:

-- 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 timestamptz)
SERVER mysql_svr OPTIONS ();

-- Postgres query
insert into f_mysql_test_timestamp values (1, '2020-01-01 00:00:00-05');
INSERT 0 1
select * from f_mysql_test_timestamp ;
 a |           b            
---+------------------------
 1 | 2020-01-01 05:00:00-05
(1 row)

-- mysql query:
mysql> set session time_zone='+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from mysql_test_timestamp;
+---+---------------------+
| a | b                   |
+---+---------------------+
| 1 | 2020-01-01 10:00:00 | <-- UTC/GMT value
+---+---------------------+
1 row in set (0.00 sec)

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:

  mysql_fdw/mysql_query.c

  Line 352 in 9204d9e

  timestamp2tm(valueTimestamp, &tz, tm, &fsec, &tzn,

  
  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.

[surajkharage19]

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?

[nxhai98]

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.

• MySQL's TIMESTAMP have time zone information implicitly, it is a "physical" instant (like the moment of an actual physical event). It has same UTC epoch in all time zones.
• PostgreSQL's 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:

• With Postgres normal table: 2020-01-01 00:00:00 is Postgres timestamp and stored value in PostgreSQL same with 2020-01-01 00:00:00 UTC
• With mysql_fdw foreign table: 2020-01-01 00:00:00 stored as 2019-12-31 18:30:00 UTC in MySQL

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?

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

[surajkharage19]

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.

[surajkharage19]

Hi @nxhai98,

We have committed the changes for the timestamp data type as reported by you. Please check on this.