Query 20240119_063836_00856_pugph failed: Unsupported column type: timestamp

[JxKim]

I was trying to use MySql connector and inserting datas which are results selected from other tables into the mysql table.
There is a column with datetime type in the dest table. When I executed the 'insert into select' query, an exception occurred.
Now I can only create a tmp table in MySql and use it as a bridge. Why timestamp not supported yet???

• Presto version used:0.280-A95C1B4
• Storage (HDFS/S3/GCS..):
• Data source and connector used: mysql connector

Possible Solution

Steps to Reproduce

Screenshots (if appropriate)

[yhwang]

Can you provide the stack trace and SQL you are using? I guess you tried to upload a screenshot but the link just points back to this issue.

[Akanksha-kedia]

@JxKim According to me, what i understood from mentioned above "The issue you're encountering is likely due to the way Presto handles TIMESTAMP data types. Presto has its own internal representation for TIMESTAMP, and it may not always map perfectly to the TIMESTAMP or DATETIME types in MySQL.

As of now, the Presto MySQL connector only supports a subset of MySQL's data types, and the support for TIMESTAMP is limited. This is a known limitation and is documented in the Presto's official documentation.

As a workaround, you could convert the TIMESTAMP columns to a string format that Presto can handle, perform your operations, and then convert them back to TIMESTAMP in MySQL if necessary. You could also consider using a different connector that fully supports TIMESTAMP, like the PostgreSQL connector, if switching databases is an option for you."

[JxKim]

@JxKim According to me, what i understood from mentioned above "The issue you're encountering is likely due to the way Presto handles TIMESTAMP data types. Presto has its own internal representation for TIMESTAMP, and it may not always map perfectly to the TIMESTAMP or DATETIME types in MySQL.

As of now, the Presto MySQL connector only supports a subset of MySQL's data types, and the support for TIMESTAMP is limited. This is a known limitation and is documented in the Presto's official documentation.

As a workaround, you could convert the TIMESTAMP columns to a string format that Presto can handle, perform your operations, and then convert them back to TIMESTAMP in MySQL if necessary. You could also consider using a different connector that fully supports TIMESTAMP, like the PostgreSQL connector, if switching databases is an option for you."

why is mysql connector not supporting datetime mapping yet? Are there any technical reasons for this?

[yhwang]

This is no technical issue/reason for this limitation. If you'd like to have a PR for this, it would be great!

[hantangwangd]

@Akanksha-kedia @yhwang @JxKim I have rose a PR to support timestamp in mysql connector. Please take a look, thanks!