Timezone being interpreted incorrectly

[kychanbp]

P.S. Don't attach files. Please, prefer add code snippets directly in the message body.

The source table is MySQL (Though I am not sure whether MySQL has timezone in datetime type [I think no timezone info]). I have 3 columns with data type datetime. When I try to extract tables using

query = """
        select {prefix}{table}.* 
        from {prefix}{table}
        """.format(prefix=prefix, table=table)  

df = wr.db.read_sql_query(query, con=sql_engine)

The result pandas dataframe automatically changed the datetime value.
Pandas dataframe

transaction_date                 created_on                modified_on
0     2017-10-04 00:00:00 2018-10-05 10:56:39.000000 2018-10-05 10:56:39.000000
1     2017-10-04 00:00:00 2018-10-05 10:56:39.000000 2018-10-05 10:56:39.000000
2     2017-10-04 00:00:00 2018-10-05 10:56:39.000000 2018-10-05 10:56:39.000000
3     2017-10-04 00:00:00 2018-10-05 10:56:39.000000 2018-10-05 10:56:39.000000
4     2017-10-04 00:00:00 2018-10-05 10:56:39.000000 2018-10-05 10:56:39.000000
...                   ...                        ...                        ...
33901 2020-11-05 06:20:25 2020-11-06 06:20:34.100835 2020-11-06 06:20:34.100877
33902 2020-11-05 06:20:34 2020-11-06 06:20:43.551528 2020-11-06 06:20:43.551551
33903 2020-11-05 06:34:58 2020-11-06 06:35:34.719571 2020-11-06 06:35:34.719592
33904 2020-11-05 06:35:34 2020-11-06 06:35:45.229119 2020-11-06 06:35:45.229141
33905 2020-11-05 06:35:45 2020-11-06 06:36:03.171246 2020-11-06 06:36:03.171269

while the source should be

transaction_date                 created_on                modified_on
2017-10-04 08:00:00	2018-10-05 18:56:39	2018-10-05 18:56:39
2017-10-04 08:00:00	2018-10-05 18:56:39	2018-10-05 18:56:39
2017-10-04 08:00:00	2018-10-05 18:56:39	2018-10-05 18:56:39
2017-10-04 08:00:00	2018-10-05 18:56:39	2018-10-05 18:56:39

my timezone is UTC+8 (Hong Kong)

[igorborgest]

Hi @kychanbp

By default Wrangler is using the GLOBAL timezone configured in your MySQL. But you can overwrite it at the session level.

E.g. 1

import wrangler as wr

eng = wr.catalog.get_engine(
    connection="aws-data-wrangler-mysql",
    connect_args={
        "init_command": "SET SESSION time_zone='-03:00'"
    }
)

wr.db.read_sql_query("SELECT NOW()", eng)

E.g. 2

import wrangler as wr

eng = wr.catalog.get_engine(
    connection="aws-data-wrangler-mysql",
    connect_args={
        "init_command": "SET SESSION time_zone='Brazil/East'"
    }
)

wr.db.read_sql_query("SELECT NOW()", eng)

References:

1. https://dev.mysql.com/doc/refman/8.0/en/datetime.html
2. https://pymysql.readthedocs.io/en/latest/modules/connections.html

[kychanbp]

@igorborgest I have specified the timezone in the session level. However, it does not change the result. The resulting dataframe datetime is still being subtracted by 8 hours.

[igorborgest]

@kychanbp

Weird... Maybe you the timezone configured in your database is UTC in the end.

Did you try?

eng = wr.catalog.get_engine(
    connection="aws-data-wrangler-mysql",
    connect_args={
        "init_command": "SET SESSION time_zone='+00:00'"
    }
)

Can you also check your global time zone, please?

[kychanbp]

@igorborgest
I have run SELECT @@global.time_zone, @@session.time_zone; The result is UTC UTC. I I have set the timezone like this:

sql_engine = wr.catalog.get_engine(connection,
    connect_args={
        "init_command": "SET SESSION time_zone='+00:00'"
    })

But the extracted pandas dataframe is still being subtracted by 8 hours.