New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

ODBC data type -155 is not supported. #134

Open
joshuaduffy opened this Issue Aug 23, 2016 · 6 comments

Comments

Projects
None yet
6 participants
@joshuaduffy

joshuaduffy commented Aug 23, 2016

Hello,

I'm working with a MSSQL database that uses DATETIMEOFFSET's for date storage. However the type is unsupported, would you consider adding it in? DATETIMEOFFSET is an "UPPERCASE" type and as I understand quite a common way to store TZ info with a timestamp in SQL Server.

I can work around it with casting, but it would be great to see proper support. Finally, thanks for all your work on this so far as well, really happy with it and it is serving me very well :)

Example: 2016-08-17 10:08:56.18 +00:00

I'm using v3.0.10 on Windows with Python 3.5.2 if that helps.

@mkleehammer

This comment has been minimized.

Owner

mkleehammer commented Jan 16, 2017

Two quick workarounds until I figure out if I can make something official:

  1. You can always add an output converter function which will receive the value as a byte array. You can then convert to the right Python object and return it.
  2. I've modified the default behavior to return unknown data types as byte arrays.

@mkleehammer mkleehammer added the Request label Jan 16, 2017

@stevenwinfield

This comment has been minimized.

stevenwinfield commented Feb 22, 2017

If it helps, I just wrote an output converter function for this.

The binary format is this:
struct.Struct("<6hI2h")
...which unpacks 20 bytes of data into a tuple of 9 values:
year, month, day, hours, minutes, seconds, nanoseconds, offset_hours, offset_minutes
...which can be used to construct a datetime object.

If the timezone offset is negative, both offset_hours and offset_minutes will be negative.

You'll need a tzinfo implementation if you want your datetimes to be timezone aware - FixedOffsetTimezone from psycopg2.tz does the job.
Also remember that datetime's constructor accepts microseconds, not nanoseconds:

total_offset_minutes = offset_hours * 60 + offset_minutes
result = datetime.datetime(year, month, day, hours, minutes, seconds, nanoseconds // 1000, tzinfo=FixedOffsetTimezone(total_offset_minutes))

@gordthompson

This comment has been minimized.

Contributor

gordthompson commented Mar 17, 2017

While it's nice to have the option of creating an output converter function when our code uses pyodbc directly, it does still leave cases like this question on Stack Overflow where we're at the mercy of what the ORM (or whatever) does with pyodbc on our behalf.

@akifkamal

This comment has been minimized.

akifkamal commented Aug 1, 2018

This is for reading data from the database. What about if we want to store datetime with timezone?

@v-chojas

This comment has been minimized.

Contributor

v-chojas commented Aug 2, 2018

You can always supply parameters as string and let ODBC driver/database do the conversion.

@gordthompson

This comment has been minimized.

Contributor

gordthompson commented Aug 3, 2018

Further to comment from @v-chojas I added an example of the required string formatting here.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment