request: consistent handling of timezone-aware datetime parameter values (with datetimeoffset columns)

[gordthompson]

Further to #134, and related to pandas-dev/pandas#30884 I'm adding this as a "wish list" item.

Python version 3.8.5 (tags/v3.8.5:580fbb0, Jul 20 2020, 15:43:08) [MSC v.1926 32 bit (Intel)]
pyodbc version 4.0.30
driver: msodbcsql17.dll, version: 17.05.0002

connection string:
DRIVER=ODBC Driver 17 for SQL Server;SERVER=localhost,49242;DATABASE=mydb;Trusted_Connection=Yes;UseFMTONLY=Yes;

When a timezone-aware datetime object

# test data
my_tz = datetime.timezone(datetime.timedelta(hours=-7))
dto_value = datetime.datetime(2020, 1, 1, 0, 0, 0, tzinfo=my_tz)
print(dto_value)  # 2020-01-01 00:00:00-07:00
#                                        ^

is passed as a parameter value …

• execute, and
• executemany with fast_executemany = False

… drop the timezone component:

exec sp_prepexec @p1 output,N'@P1 int,@P2 datetime2',N'INSERT INTO DateTimeOffset_Test (id, dto) VALUES (@P1, @P2)',0,'2020-01-01 00:00:00'

while executemany with fast_executemany = True inserts the correct value

exec sp_prepare @p1 output,N'@P1 int,@P2 datetimeoffset',N'INSERT INTO DateTimeOffset_Test (id, dto) VALUES (@P1, @P2)',1
exec sp_execute 1,0,'2020-01-01 00:00:00 -07:00'
exec sp_unprepare 1

Since the latter example shows that it "can be done", it would be nice if the handling of such parameter values was consistent.

[mkleehammer]

I would very much like them to be consistent.

I'm just not sure what the right answer is. Is the DB always in UTC and we should therefore convert it?

That seems a bit dangerous since there is no way for us to really know that. (Unless the DB has a type that tells us that. I could see making PostgreSQL's timestamptz work properly.)

I'm open to it if it won't break a bunch of people.

[gordthompson]

The first fix that comes to mind is that if the datetime object is timezone-aware then render the parameter value as a string in RFC 3339 format. I just tested against SQL Server and both

select cast('1985-04-12T23:20:50.52Z' as datetimeoffset) as foo

and

select cast('1996-12-19T16:39:57-08:00' as datetimeoffset) as foo

seemed to work fine.

[hb2638]

I would very much like them to be consistent.

I'm just not sure what the right answer is. Is the DB always in UTC and we should therefore convert it?

That seems a bit dangerous since there is no way for us to really know that. (Unless the DB has a type that tells us that. I could see making PostgreSQL's timestamptz work properly.)

I'm open to it if it won't break a bunch of people.

I’m making assumptions here…

assuming you know the type of db engine you’re connecting to, you should always send datetime objects (not to be confused with python date objects) as datetimeoffest when the tz is not null and send it as datetime2 of the tz is missing when you know you’re connected to mssql. Sql server does implicit conversion of datetimeoffset to datetime and datetime2, so no regressions introduced there.

mssql table value parameters have a schema so there’s no guesssing as to what it should be sent as but I’m sure callers will encounter gotchas with sending python datetime objects in various tz offsets to a tvp that has a datetime2 column.

If that’s too much work, maybe you can give us an “input converter”, so we can control how values are sent. We already have an output converter that does the opposite.

[hb2638]

I'm using this as a workaround for the moment which converts datetime objects with a timezone to a string

def fixup_args(cursor: pyodbc.Cursor, args: typing.List) -> typing.List:
    return [a.isoformat() if isinstance(a, datetime) and a.tzinfo else a for a in args] if cursor.connection.getinfo(pyodbc.SQL_DBMS_NAME) == "Microsoft SQL Server" else args


cnxn = pyodbc.connect(cnxn_str, autocommit=True)
cnxn.add_output_converter(-155, handle_datetimeoffset)
crsr = cnxn.cursor()
expected = datetime(2022, 12, 27, 0, 0, 0, tzinfo=ZoneInfo("America/New_York"))
args = [expected]
args = fixup_args(args)
crsr.execute("DECLARE @INPUT DATETIMEOFFSET=?; SELECT @INPUT AS [output]", args)
actual = crsr.fetchone().output