JDBC Sources: Consolidate putDate behavior, and potentially other methods

[edgao]

Multiple sources override the default putDate implementation (

airbyte/airbyte-db/db-lib/src/main/java/io/airbyte/db/jdbc/AbstractJdbcCompatibleSourceOperations.java

Lines 126 to 128 in 0092712

	protected void putDate(final ObjectNode node, final String columnName, final ResultSet resultSet, final int index) throws SQLException {
	node.put(columnName, DataTypeUtils.toISO8601String(resultSet.getDate(index)));
	}

), because it's incorrect. Specifically, it produces a full ISO8601 timestamp, when dates should just be YYYY-MM-DD.

Postgres and Mysql currently have the exact same implementation (https://github.com/airbytehq/airbyte/blob/master/airbyte-integrations/connectors/source-postgres/src/main/java/io/airbyte/integrations/source/postgres/PostgresSourceOperations.java#L214-L217), and we intend to add that to Snowflake as well. Would be good to just modify the base class and fix this for all JDBC sources.

---

We should probably also check for other methods in a similar situation, and consolidate them too.

[VitaliiMaltsev]

@edgao i have made a small investigation into DATE datatype in different databases
For the most of them YYYY-MM-DD is a valid pattern for DATE but also we have exceptions like OracleDb
DATE in OracleDb could contain not only year/month/day but also time so for Oracle yyyy-MM-dd'T'HH:mm:ss is a valid pattern

1. Should we use the common putDate for all jdbc sources with pattern YYYY-MM-DD and override it for OracleDb?
2. Which is a correct way to handle DATE column if it is a cursor field in incremental sync and all of previously synced records have full ISO8601 timestamp pattern?

[edgao]

if an oracle DATE column can include a timestamp... shouldn't we just discover it as a timestamp without timezone?

[VitaliiMaltsev]

if an oracle DATE column can include a timestamp... shouldn't we just discover it as a timestamp without timezone?

yes, we can do it. Do I understand you correctly that it is necessary to do what I described in paragraph 1?
OracleDB has also separate TIMESTAMP data type which could also include the same values

[edgao]

yeah, that sounds right. I.e. most DB sources would continue to discover date fields, and write YYYY-MM-DD values; only source-oracle would discover timestamp_without_timezone and write full timestamp values

for previously-synced data - wouldn't the time component always be 00:00:00? so we can always just ignore it

[VitaliiMaltsev]

blocked by #20634