[GitHub] [spark] huaxingao commented on pull request #29043: [SPARK-32205][SQL] Writing timestamp to mysql should be datetime type
huaxingao commented on pull request #29043:
URL: https://github.com/apache/spark/pull/29043#issuecomment-657179299
@TJX2014
I am still not convinced that it's a good idea to map TimeStamp to DateTime
in MySQLDialect. Using an example similar to yours:
```
sql("select cast('1970-01-01 00:00:01' as
timestamp)").toDF("ts").write.mode("append").jdbc("jdbc:mysql://localhost:3306/test",
"ts_test3",prop)
```
Since user explicitly cast string to timestamp, I would think that the user
wants to insert 1970-01-0 00:00:01 as a TimeStamp data type. Suppose the
current time zone on mysql server is america/los_angeles. After the data is
inserted to mysql, if the user changes the time zone setting
SET TIME_ZONE = "america/new_york";
Then the user would expect to get 1970-01-01 03:00:01 when retrieving the
timestamp. But with this patch, we silently change the data type from TimeStamp
to DateTime, after user insert the timestamp and change the time zone setting,
user will still get 1970-01-0 00:00:01 which is not the correct value.
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
-
To unsubscribe, e-mail: reviews-unsubscr...@spark.apache.org
For additional commands, e-mail: reviews-h...@spark.apache.org
[GitHub] [spark] huaxingao commented on pull request #29043: [SPARK-32205][SQL] Writing timestamp to mysql should be datetime type
huaxingao commented on pull request #29043: URL: https://github.com/apache/spark/pull/29043#issuecomment-657009038 @TJX2014 Actually, regardless of time zone, map `timestamp` to `datetime` in mysql dialect doesn't seem to solve your problem. Suppose you create a table with a `timestamp` column CREATE TABLE test (c timestamp); You want to insert a timestamp '-01-01 00:00:01' to TABLE test. Before your fix, when inserting this timestamp '-01-01 00:00:01' to table test, mysql JDBC driver does the range check for this timestamp value and throw Exception because timestamp '-01-01 00:00:01' is not valid (mysql TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC) After your fix, when inserting this timestamp '-01-01 00:00:01' to table test, since you change the data type to `datetime` now, mysql JDBC driver doesn't throw Exception since '-01-01 00:00:01' is a valid `datetime` value, but when inserting this value into table test, because the real type of column c is timestamp, mysql will throw Exception since '-01-01 00:00:01' is not a valid timestamp value. I don't have mysql database and JDBC driver on my local to test this, but in theory it works this way. You may want to try to see if you can insert this timestamp '-01-01 00:00:01' OK. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org - To unsubscribe, e-mail: reviews-unsubscr...@spark.apache.org For additional commands, e-mail: reviews-h...@spark.apache.org
[GitHub] [spark] huaxingao commented on pull request #29043: [SPARK-32205][SQL] Writing timestamp to mysql should be datetime type
huaxingao commented on pull request #29043: URL: https://github.com/apache/spark/pull/29043#issuecomment-656753581 @TJX2014 Seems MySQL TIMESTAMP is affected by the time_zone setting but DATETIME is not (https://www.tech-recipes.com/rx/22599/mysql-datetime-vs-timestamp-data-type/). I think you will lose the time_zone info if you use DATETIME for TIMESTAMP. This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org - To unsubscribe, e-mail: reviews-unsubscr...@spark.apache.org For additional commands, e-mail: reviews-h...@spark.apache.org
