✨ Add support for converting TIMESTAMP WITH LOCAL TIME ZONE to exasol_mapper

[Nicoretti]

Summary

The datatype mapper provided by pyexasol (pyexasol.mapper.exasol_mapper) currently does not support TIMESTAMP WITH LOCAL TIME ZONE, which may cause inconvenience to the user. Adding support for this data type would improve the usability of pyexasol.

Details

The pyexasol library provides a datatype mapper to convert the Exasol data types to Python data types. However, the current implementation does not support the TIMESTAMP WITH LOCAL TIME ZONE data type. This means that when a user tries to fetch data from a column of this type, they will receive a string.

Until this is addressed, we suggest creating a custom mapper that adds support for TIMESTAMP WITH LOCAL TIME ZONE (see examples).

Examples

Current behavior

import pyexasol
from pyexasol import mapper

con = pyexasol.connect(dsn='127.0.0.1:8563', user='sys', password='exasol', fetch_mapper=mapper.exasol_mapper)

# This returns a datetime.datetime object
query = "SELECT CAST('2022-01-01 01:00:00.000000' AS TIMESTAMP WITH LOCAL TIME ZONE) AS timestamp_col"
result = con.execute(query).fetchall()
print(result)

# This returns a string object
query = "SELECT CAST('2022-01-01 01:00:00.000000' AS TIMESTAMP WITH LOCAL TIME ZONE) AS timestamp_col"
result = con.execute(query).fetchall()
print(result)

Custom mapper adding support for TIMESTAMP WITH LOCAL TIME ZONE

import pyexasol
from pyexasol import mapper
from dateutil.tz import tzlocal

def my_mapper(value, data_type):
    if not value:
            return None
    if data_type['type'] == "TIMESTAMP WITH LOCAL TIME ZONE":
            # convert to datetime object with local timezone
            value = value.astimezone(tzlocal())
    return mapper.exasol_mapper(value, data_type)

con = pyexasol.connect(dsn='127.0.0.1:8563', user='sys', password='exasol', fetch_mapper=my_mapper)