Thanks for logging this. It was a subtle issue that we have overlooked for many years.
Please change “Timestamp” to “TIMESTAMP” so it’s clear we are taking about the SQL type not the Java type. Also copy-paste into the description the relevant text from the JDBC spec. Please broaden the scope so that we also fix for DATE,TIME, TIMESTAMP WITH TIME ZONE, etc. Julian > On Aug 20, 2025, at 8:25 PM, Yanjing Wang <zhuangzixiao...@gmail.com> wrote: > > Thanks Istvan, I logged it. CALCITE-7143 > <https://issues.apache.org/jira/browse/CALCITE-7143> > > Istvan Toth <st...@cloudera.com.invalid> 于2025年8月19日周二 13:32写道： > >> While the JDBC spec often leaves a lot to interpretation, in this case it >> is explicit: >> https://docs.oracle.com/javase/8/docs/api/java/sql/ResultSetMetaData.html >> Please open a JIRA ticket for the getPrecision issue. >> >>> On Mon, Aug 18, 2025 at 2:33 PM Yanjing Wang <zhuangzixiao...@gmail.com> >>> wrote: >>> >>> Hi Justin, Thank you for your detailed explanation about timestamp >>> precision handling across different databases. While investigating this >>> further, I noticed an important difference in how precision is >> interpreted: >>> In MySQL, ResultSetMetadata#getPrecision() returns the total length of >> the >>> timestamp string representation (including year, month, day, hours, >>> minutes, seconds, and fractional parts if any). However, in Avatica, it >>> seems the precision value specifically represents the number of >> fractional >>> digits after the decimal point in seconds. For example: - MySQL: for >>> 'yyyy-MM-dd HH:mm:ss.ffffff', getPrecision() would return the total >> string >>> length - Avatica: for the same timestamp, getPrecision() would return 6 >>> (counting only the fractional digits), see DateTimeUtils#unixTimeToString >>> method in avatica. Could you confirm if this is the intended behavior for >>> Avatica? Should the precision value specifically represent the fractional >>> seconds digits rather than the total string length? This distinction >> seems >>> important for ensuring correct handling across different implementations. >>> Thank you for your help in clarifying this. Best regards, Yanjing Wang >>> >>> Justin Swanhart <greenl...@gmail.com> 于2025年8月18日周一 18:44写道： >>> >>>> TIMESTAMP values in MySQL (and I think Clickhouse and Doris) can return >>>> fractional seconds but only when requested. Try "SELECT NOW(6);" for >>>> example, which will return a fractional timestamp. The SQL standard >>>> includes 6 places of precision by default, but other databases like >> MySQL >>>> default to 0. As I understand it, Calcite follows the SQL standard and >>>> returns fractional timestamps with 6 places of precision. >>>> >>>> --Justin >>>> >>>> On Mon, Aug 18, 2025 at 4:31 AM Yanjing Wang < >> zhuangzixiao...@gmail.com> >>>> wrote: >>>> >>>>> Hello Community, I hope this email finds you well. I'm investigating >>> the >>>>> expected behavior of ResultSet#getString() method when dealing with >>>>> Timestamp column type across different implementations. I've noticed >>> that >>>>> Avatica's getString() returns Timestamp values in the format >>> 'yyyy-MM-dd >>>>> HH:mm:ss.ppppp...' (where the count of 'p' matches the precision >>> value), >>>>> while some other SQL engines like MySQL, ClickHouse and Apache Doris >>>> return >>>>> the format 'yyyy-MM-dd HH:mm:ss' without fractional seconds. This >>>>> difference in format handling raises some questions: 1. Is the format >>>>> 'yyyy-MM-dd HH:mm:ss.ppppp...' with precision the intended standard >>>>> behavior for Avatica's ResultSet#getString()? 2. Should other >>>>> implementations (like MySQL, ClickHouse and Doris connectors) that >> use >>>>> Avatica protocol align with this format? 3. Are there any specific >>>>> considerations or reasons for including/excluding the fractional >>> seconds >>>> in >>>>> the string representation? Current observations: - Avatica: returns >>>>> 'yyyy-MM-dd HH:mm:ss.ppppp...' (with precision) - MySQL, ClickHouse, >>>> Apache >>>>> Doris: returns 'yyyy-MM-dd HH:mm:ss' >>>>> Understanding the standard expectation would help ensure consistency >>>> across >>>>> different implementations. Thank you for your time and guidance. Best >>>>> regards, Yanjing Wang >>>>> >>>> >>> >> >> >> -- >> *István Tóth* | Sr. Staff Software Engineer >> *Email*: st...@cloudera.com >> cloudera.com <https://www.cloudera.com> >> [image: Cloudera] <https://www.cloudera.com/> >> [image: Cloudera on Twitter] <https://twitter.com/cloudera> [image: >> Cloudera on Facebook] <https://www.facebook.com/cloudera> [image: Cloudera >> on LinkedIn] <https://www.linkedin.com/company/cloudera> >> ------------------------------ >> ------------------------------ >>
