Supported type conversions in v2

[gingerwizard]

v1 of this driver allowed different types to be passed for a column at insertion type, with this necessary type conversion supported implicitly. Whilst this was convenient, it also allowed the potential loss of precision. Our aspiration for v2 is to support all possible implicit conversions that can be done safely with no precision loss.

Currently, v2 has a number of functional gaps where no precision would be lost and conversion could be safely performed. This issue will be used to track current gaps and the eventual compatibility matrix.

Current support recorded here.

Known gaps:

• Insert string to datetime64
• Insert string to datetime
• Insert string to date
• sql.Null* types Support sql.Null* types #682

[gwvandesteeg]

Can you add String to UUID and vice versa, especially for those of us that don't use the google UUID library (github.com/google/uuid), but alternatives such as github.com/gofrs/uuid, which for us has a much cleaner API and more expansive functionality.

[quan-nguyen2]

I have the same issue with Date type, in v1, we are able to use string to represent date; but in v2, it returns the error converting string to Date is unsupported

[fiftin]

Hi @gingerwizard ,

What should be done to close this issue?

[gingerwizard]

@fiftin we need to go through and compare v1 and v2 to see where type conversions are missing that also don't loose precision. Then we need to address gaps.
Right now I'm addressing as users report.

Noting @quan-nguyen2 issue I'll fix in next release.

[fiftin]

@gingerwizard

So: time & date to/from string conversion? That's all?

[gingerwizard]

No im sure there are more cases we have gaps, I just haven't done a complete audit.

[gingerwizard]

Currently supported conversions now documented here. This will be merged to main. We welcome PRs to add X's.

[DGuang21]

hi! @gingerwizard
I want to complete the type conversions of string to datetime, and I have completed part of it now, but I am worried that it will not meet the code specifications. Can you tell me whether the implementation of this PR is feasible? https://github.com/DGuang21/clickhouse-go/pull/1

The principle is to match the text through regular rules, and if it is a text time type that conforms to the rules, it will be converted.

[gingerwizard]

@DGuang21 if we support text im hesitant to support anything other than the default format supported by ClickHouse. Maybe we could consider https://clickhouse.com/docs/en/operations/settings/settings/#date_time_input_format but I'm not sure I want to get into supporting lots of date formats with different regexes.

Users can always use https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions/#parsedatetimebesteffort if needed.

[DGuang21]

@DGuang21 if we support text im hesitant to support anything other than the default format supported by ClickHouse. Maybe we could consider https://clickhouse.com/docs/en/operations/settings/settings/#date_time_input_format but I'm not sure I want to get into supporting lots of date formats with different regexes.

Users can always use https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions/#parsedatetimebesteffort if needed.

Thanks for your reply . So should/can I go about doing string-to-time that supports a large number of types? Or like datetime64 only supports default type of time.

I'm also not sure if users really need it, but time types in business can be relatively many and complex, I just wrote a demo code based on the foundation of that can be done safely with no precision loss.

[gingerwizard]

Thanks for your reply . So should/can I go about doing string-to-time that supports a large number of types? Or like datetime64 only supports default type of time.

I would stay to the default. If there is a library that is well supported, written and tested for datetime parsing I would be potentially interested in this. I'd rather this code not live in the client, though.

[gingerwizard]

As an example, https://github.com/araddon/dateparse/issues has over 1.7k stars and still have issues and no commits in 2yrs. We open the can to support formats we have to take issues on it. I'd rather a simple line of "default" or do it outside of the client.

[gwvandesteeg]

Thanks for your reply . So should/can I go about doing string-to-time that supports a large number of types? Or like datetime64 only supports default type of time.

I would stay to the default. If there is a library that is well supported, written and tested for datetime parsing I would be potentially interested in this. I'd rather this code not live in the client, though.

There's a reason there's an ISO8601 standard for representing date and time in an unambiguous and lexographically sorted manner that are supported by go with default format strings using time.RFC3339 and time.RFC3339Nano (with caveats on this one). Supporting these out of the box are relatively straight forward to add and are commonly used in many systems and as such should really be the default supported format, and include a rather important suggestion in their documentation RFC3339 should be preferred for new protocols.