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 <[email protected]> 于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 <[email protected]> > 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 > > >
