ExecuteSQL doesn't select table if it having dateTime Offset value?

Question

I have created table with single column having data type -dateTimeOffset value and inserted some values.

create table dto (dto datetimeoffset(7))
insert into dto values (GETDATE()) -- inserts date and time with 0 offset
insert into dto values (SYSDATETIMEOFFSET()) -- current date time and offset
insert into dto values ('20131114 08:54:00 +10:00') -- manual way

In Nifi,i have specified "Select * from dto" query in Execute SQL .

It shows below error..,

java.lang.IllegalArgumentException: createSchema: Unknown SQL type -155 cannot be converted to Avro type

If i change that column into dateTime then ExecuteSQL runs correctly but it doesn't worked in DateTimeOffset column.

Any help appreciated.

Many thanks

Answer 1

datetimeoffset is a MSSQL-specific JDBC type and is not supported by ExecuteSQL (which supports the standard JDBC types). You could try to cast the datetimeoffset field into some other standard type such as datetime, as described here.

Answer 2

I've created a Custom Processor and adapted the JdbcCommon.java class to include SQL Server's DATETIMEOFFSET. It's just one line of code. I'll try to see if I can ask them to merge this on the official repo.

This is a piece of my JdbcCommon.java:

                case TIMESTAMP:
                case TIMESTAMP_WITH_TIMEZONE:
                case -101: // Oracle's TIMESTAMP WITH TIME ZONE
                case -102: // Oracle's TIMESTAMP WITH LOCAL TIME ZONE
                case -155: // SQL Server's DATETIMEOFFSET <---- added this line
                    addNullableField(builder, columnName,
                            u -> options.useLogicalTypes
                                    ? u.type(LogicalTypes.timestampMillis().addToSchema(SchemaBuilder.builder().longType()))
                                    : u.stringType());
                    break;