BUG: sql different offsets (1/2 modify infer_dtype)
- [x] closes #30207
- [x] tests added / passed
- [x] passes `black pandas`
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry

# Explanations

For to_sql no errors are raised anymore when we have Series containing datetimes with timezone and different offsets (unlike in GH30207) and the data is saved as expected (I tested with postgres which does not save offsets anyways).

Concerning read_sql however there are several points to discuss:

1. if we want to have datetime64 instead of "object" (as described in [GH30207](https://github.com/pandas-dev/pandas/issues/30207)) we lose the offsets. So this would be a breaking change and would require first a warning in the next version then we implement it in the version after, right?

2. is this patch I made acceptable?

The probem is that read_sql uses pd.DataFrame.from_records and it reads datetimes with different offsets as "object" instead of datetime64[ns,...] (e.g. datetime64[ns, psycopg2.tz.FixedOffsetTimezone(offset=60, name=None)]).

If we wanted to modify this behavior we would have to modify the C extension _libs.lib. I don't know how to help with that and I am not sure we want to do this. Even if we could pack the datetimes with differents offsets into a Series with a datetime64 dtype the methods of the .dt accessor are not compatible anyways: currently a ValueError is thrown if one attempts to use the accessor in this case (see traceback in the example in "Before PR" below).



# Before PR


```python
import datetime
import psycopg2
import sqlalchemy
import pandas as pd
from pandas.io.sql import read_sql, to_sql

# CREATE ENGINE
# engine = sqlalchemy.create_engine("CONNECTION_STRING")

# create datetimes with timezone that have different offsets
data = [[datetime.datetime(2019, 11, 14, 16, 12, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=60, name=None))],
        [datetime.datetime(2019, 8, 7, 15, 37, 4, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=120, name=None))]]

df = pd.DataFrame(data, columns = ['ts_tz'])

# save data
to_sql(frame = df, 
       name = 'timezone_diff_offsets_test',
       con = engine,
       schema = 'public',
       if_exists = 'replace',
       index = False)
```

<details>
 <summary>Traceback</summary>


```python-traceback

    ---------------------------------------------------------------------------

    ValueError                                Traceback (most recent call last)

    ~/GitHub/pandas/pandas/core/arrays/datetimes.py in objects_to_datetime64ns(data, dayfirst, yearfirst, utc, errors, require_iso8601, allow_object)
       1968         try:
    -> 1969             values, tz_parsed = conversion.datetime_to_datetime64(data)
       1970             # If tzaware, these values represent unix timestamps, so we


    ~/GitHub/pandas/pandas/_libs/tslibs/conversion.pyx in pandas._libs.tslibs.conversion.datetime_to_datetime64()


    ValueError: Array must be all same time zone

    
    During handling of the above exception, another exception occurred:


    ValueError                                Traceback (most recent call last)

    <ipython-input-2-a66775d04fe3> in <module>
         20        schema = 'public',
         21        if_exists = 'replace',
    ---> 22        index = False)
    

    ~/GitHub/pandas/pandas/io/sql.py in to_sql(frame, name, con, schema, if_exists, index, index_label, chunksize, dtype, method)
        519         chunksize=chunksize,
        520         dtype=dtype,
    --> 521         method=method,
        522     )
        523 


    ~/GitHub/pandas/pandas/io/sql.py in to_sql(self, frame, name, if_exists, index, index_label, schema, chunksize, dtype, method)
       1318             index_label=index_label,
       1319             schema=schema,
    -> 1320             dtype=dtype,
       1321         )
       1322         table.create()


    ~/GitHub/pandas/pandas/io/sql.py in __init__(self, name, pandas_sql_engine, frame, index, if_exists, prefix, index_label, schema, keys, dtype)
        620         if frame is not None:
        621             # We want to initialize based on a dataframe
    --> 622             self.table = self._create_table_setup()
        623         else:
        624             # no data provided, read-only mode


    ~/GitHub/pandas/pandas/io/sql.py in _create_table_setup(self)
        869         from sqlalchemy import Table, Column, PrimaryKeyConstraint
        870 
    --> 871         column_names_and_types = self._get_column_names_and_types(self._sqlalchemy_type)
        872 
        873         columns = [


    ~/GitHub/pandas/pandas/io/sql.py in _get_column_names_and_types(self, dtype_mapper)
        861         column_names_and_types += [
        862             (str(self.frame.columns[i]), dtype_mapper(self.frame.iloc[:, i]), False)
    --> 863             for i in range(len(self.frame.columns))
        864         ]
        865 


    ~/GitHub/pandas/pandas/io/sql.py in <listcomp>(.0)
        861         column_names_and_types += [
        862             (str(self.frame.columns[i]), dtype_mapper(self.frame.iloc[:, i]), False)
    --> 863             for i in range(len(self.frame.columns))
        864         ]
        865 


    ~/GitHub/pandas/pandas/io/sql.py in _sqlalchemy_type(self, col)
        971             # timezone information
        972             try:
    --> 973                 if col.dt.tz is not None:
        974                     return TIMESTAMP(timezone=True)
        975             except AttributeError:


    ~/GitHub/pandas/pandas/core/accessor.py in _getter(self)
         83         def _create_delegator_property(name):
         84             def _getter(self):
    ---> 85                 return self._delegate_property_get(name)
         86 
         87             def _setter(self, new_values):


    ~/GitHub/pandas/pandas/core/indexes/accessors.py in _delegate_property_get(self, name)
         60         from pandas import Series
         61 
    ---> 62         values = self._get_values()
         63 
         64         result = getattr(values, name)


    ~/GitHub/pandas/pandas/core/indexes/accessors.py in _get_values(self)
         51                 return PeriodArray(data, copy=False)
         52             if is_datetime_arraylike(data):
    ---> 53                 return DatetimeIndex(data, copy=False, name=self.name)
         54 
         55         raise TypeError(


    ~/GitHub/pandas/pandas/core/indexes/datetimes.py in __new__(cls, data, freq, tz, normalize, closed, ambiguous, dayfirst, yearfirst, dtype, copy, name)
        266             dayfirst=dayfirst,
        267             yearfirst=yearfirst,
    --> 268             ambiguous=ambiguous,
        269         )
        270 


    ~/GitHub/pandas/pandas/core/arrays/datetimes.py in _from_sequence(cls, data, dtype, copy, tz, freq, dayfirst, yearfirst, ambiguous)
        418             dayfirst=dayfirst,
        419             yearfirst=yearfirst,
    --> 420             ambiguous=ambiguous,
        421         )
        422 


    ~/GitHub/pandas/pandas/core/arrays/datetimes.py in sequence_to_dt64ns(data, dtype, copy, tz, dayfirst, yearfirst, ambiguous)
       1864             #  or M8[ns] to denote wall times
       1865             data, inferred_tz = objects_to_datetime64ns(
    -> 1866                 data, dayfirst=dayfirst, yearfirst=yearfirst
       1867             )
       1868             tz = maybe_infer_tz(tz, inferred_tz)


    ~/GitHub/pandas/pandas/core/arrays/datetimes.py in objects_to_datetime64ns(data, dayfirst, yearfirst, utc, errors, require_iso8601, allow_object)
       1972             return values.view("i8"), tz_parsed
       1973         except (ValueError, TypeError):
    -> 1974             raise e
       1975 
       1976     if tz_parsed is not None:


    ~/GitHub/pandas/pandas/core/arrays/datetimes.py in objects_to_datetime64ns(data, dayfirst, yearfirst, utc, errors, require_iso8601, allow_object)
       1963             dayfirst=dayfirst,
       1964             yearfirst=yearfirst,
    -> 1965             require_iso8601=require_iso8601,
       1966         )
       1967     except ValueError as e:


    ~/GitHub/pandas/pandas/_libs/tslib.pyx in pandas._libs.tslib.array_to_datetime()


    ~/GitHub/pandas/pandas/_libs/tslib.pyx in pandas._libs.tslib.array_to_datetime()


    ValueError: Tz-aware datetime.datetime cannot be converted to datetime64 unless utc=True


```

</details>


```python
# save datetimes with UTC timezone in different periods of the year
## create test data
data = [[datetime.datetime(2019, 11, 14, 15, 12, 0, tzinfo = datetime.timezone.utc)],
        [datetime.datetime(2019, 8, 7, 13, 37, 4,  tzinfo = datetime.timezone.utc)]]

df = pd.DataFrame(data, columns = ['ts_tz'])

## save test data
to_sql(frame = df, 
       name = 'timezone_diff_offsets_test',
       con = engine,
       schema = 'public',
       if_exists = 'replace',
       index = False)

# upon reading if you have for instance "Europe/Paris"
# configured as timezone on your local machine then you get datetimes with timezone
# that have different offsets (because they are in different periods of the year)
# due to this you have "object" dtype and not datetime64[...]
df = read_sql('SELECT * FROM timezone_diff_offsets_test', engine)
print(df,'\n')
print(df.dtypes)
```

                           ts_tz
    0  2019-11-14 16:12:00+01:00
    1  2019-08-07 15:37:04+02:00 
    
    ts_tz    object
    dtype: object


# After PR


```python
import datetime
import psycopg2
import sqlalchemy
import pandas as pd
from pandas.io.sql import read_sql, to_sql

# CREATE ENGINE
# engine = sqlalchemy.create_engine("CONNECTION_STRING")

# create datetimes with timezone that have different offsets
data = [[datetime.datetime(2019, 11, 14, 16, 12, 0, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=60, name=None))],
        [datetime.datetime(2019, 8, 7, 15, 37, 4, tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=120, name=None))]]

df = pd.DataFrame(data, columns = ['ts_tz'])

# save data - no exceptions occur
to_sql(frame = df, 
       name = 'timezone_diff_offsets_test',
       con = engine,
       schema = 'public',
       if_exists = 'replace',
       index = False)

# read data - offsets are dropped and dtype of column ts_tz is datetime64[ns, UTC]
df = read_sql('SELECT * FROM timezone_diff_offsets_test', engine)
print(df,'\n')
print(df.dtypes)
```

                          ts_tz
    0 2019-11-14 15:12:00+00:00
    1 2019-08-07 13:37:04+00:00 
    
    ts_tz    datetime64[ns, UTC]
    dtype: object


If the user gives a series of datetimes with mixed timezones, presumably they did it on purpose.  So we should try to preserve that information rather than convert to UTC.  Or am I missing something here? > If the user gives a series of datetimes with mixed timezones, presumably they did it on purpose. So we should try to preserve that information rather than convert to UTC. Or am I missing something here?

I don't think that we are really losing information here (see sections to_sql and read_sql below and please correct me if I'm wrong).

# to_sql
Mixed offsets and actually mixed timezones will prevent to_sql from working because the function io.sql._sqlalchemy_type [uses the .dt acessor](https://github.com/pandas-dev/pandas/blob/master/pandas/io/sql.py#L972-L978) and this raises a ValueError with mixed timezones. Since the offsets are not saved in the database anyways (for postgres and SQLite3 AFAIK) converting to UTC should solve the issue without changes for the user.

# read_sql
If all datetimes are in the same timezone and offset then we get a Series with dtype datetime64 and no offset.
If not - for instance in my case one datetime is in winter time and one is in summer time so that when psycopg2 adds the offsets to my local timezone (Europe/Paris) we get two different offsets (+1 and +2 hours) - then pandas does not convert it to UTC.
I find this rather confusing and I think the example below illustrates this:

<details>
 <summary>Example</summary>

```python
import datetime
import psycopg2
import sqlalchemy
import pandas as pd
import pytz
from pandas.io.sql import read_sql, to_sql

# CREATE ENGINE
# engine = sqlalchemy.create_engine("CONNECTION_STRING")

# create datetimes with timezone that have different offsets
data = [[datetime.datetime(2019, 11, 14, 15, 12, 0, tzinfo = datetime.timezone.utc),
         datetime.datetime(2019, 11, 14, 15, 12, 0, tzinfo = datetime.timezone.utc)],
        [datetime.datetime(2019, 11, 7, 13, 37, 4,  tzinfo = datetime.timezone.utc),
        datetime.datetime(2019, 8, 14, 15, 12, 0, tzinfo = datetime.timezone.utc)]]

df = pd.DataFrame(data, columns = ['ts_tz','ts_tz2'])

# save data - no exceptions occur
to_sql(frame = df, 
       name = 'timezone_diff_offsets_test',
       con = engine,
       schema = 'public',
       if_exists = 'replace',
       index = False)

# read data - offsets are dropped and dtype of column ts_tz is datetime64[ns, UTC]
df = read_sql('SELECT * FROM timezone_diff_offsets_test', engine)
print(df,'\n')
print(df.dtypes)
```
# Output of master branch

                          ts_tz                     ts_tz2
    0 2019-11-14 15:12:00+00:00  2019-11-14 16:12:00+01:00
    1 2019-11-07 13:37:04+00:00  2019-08-14 17:12:00+02:00 

    ts_tz     datetime64[ns, UTC]
    ts_tz2                 object
    dtype: object

# Output of my branch (bugfix_sql_different_offsets)
                          ts_tz                    ts_tz2
    0 2019-11-14 15:12:00+00:00 2019-11-14 15:12:00+00:00
    1 2019-11-07 13:37:04+00:00 2019-08-14 15:12:00+00:00 

    ts_tz     datetime64[ns, UTC]
    ts_tz2    datetime64[ns, UTC]
    dtype: object

</details>

EDIT: typos
 Can you add test cases? It's a little unclear what this is doing without those.

With respect to writing mixed timezones, I'm not sure about this. I actually think the ValueError getting raised makes more sense @jbrockmendel we have a president of converting timezones (which should include mixed) to UTC when reading from DBs. https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html#datetime-data-types

When writing mixed timezones, I agree with @WillAyd in that raising an error is preferable to coercing to UTC if we can't maintain the mixed timezones. So I have pushed another commit with the suggestions from @mroeschke.

EDIT: sorry I forgot to do local testing before pushing :disappointed: . I see there are some errors. I suppose they may be due to this utc=True I added. I will run some tests locally to figure this out.

I'd gladly add tests but I am really lost in test_sql.py I don't even know where to start. I suppose I could add tests in one of the functions [here](https://github.com/pandas-dev/pandas/blob/master/pandas/tests/io/test_sql.py#L1382-L1487). It would be easier if I had access to the database (of if I could use my own for local testing). Maybe I just need some more time to figure this out but if some wants to help I thought of some testing (it passed) with postgres already (see below).

<details>
<summary>Additional tests</summary>

```python
import datetime
import psycopg2 
import sqlalchemy
import pandas as pd
import pytz
from pandas.io.sql import read_sql, to_sql

# CREATE ENGINE
# engine = sqlalchemy.create_engine("CONNECTION_STRING")

# create test data
df = pd.DataFrame()

## CASE 1 (all UTC)
## no exception should occur with to_sql, 
## it should be saved as timestamp with timezone (where compatible e.g. postgres)
## read_sql should give back datetime64[ns, UTC] 
## and not object dtype (discard local offsets winter/summer time)
df['ts_utc_winter_summer'] = [datetime.datetime(2019, 11, 7, 13, 37, 4,  
                                                tzinfo=datetime.timezone.utc),
                              datetime.datetime(2019, 8, 14, 15, 12, 0, 
                                                tzinfo=datetime.timezone.utc)]

## CASE 2 (different offsets)
## to_sql should raise (it did with master already)
df['ts_diff_offset'] = [datetime.datetime(2019, 11, 14, 16, 12, 0, 
                             tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=60)),
                        datetime.datetime(2019, 8, 7, 15, 37, 4, 
                             tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=120))]

## CASE 3 (different timezones)
## to_sql should raise (it did with master already)
df['ts_diff_tz'] = [datetime.datetime(2019, 11, 14, 16, 12, 0, 
                                   tzinfo=datetime.timezone.utc),
                    datetime.datetime(2019, 11, 14, 16, 12, 0, 
                                   tzinfo=pytz.timezone('Europe/Paris'))]

## CASE 4 (naive)
## no exception should occur with to_sql, 
## it should be saved as timestamp without timezone
## read_sql should give back datetime64[ns] (it did with master already)
df['ts_naive'] = [datetime.datetime(2019, 11, 7, 13, 37, 4),
                  datetime.datetime(2019, 8, 14, 15, 12, 0)]

# show df
pd.options.display.width = 500
print(df,'\n')

# make sure we have the expected types
# where different timezones/offsets we should have an object dtype
expected_dtypes = {'ts_utc_winter_summer': 'datetime64[ns, UTC]',
                   'ts_diff_offset': 'O',
                   'ts_diff_tz': 'O',
                   'ts_naive': '<M8[ns]'}

for col in df.columns:
    assert df[col].dtype == expected_dtypes[col]

# try to_sql one column at a time (as a DataFrame subset not a Series)
for col in df.columns:
    try:
        to_sql(frame = df[[col]], 
               name = 'timezone_tests',
               con = engine,
               schema = 'public',
               if_exists = 'replace',
               index = False)

        df_db = read_sql('SELECT * FROM timezone_tests', engine)

        if col == 'ts_naive':
            assert df_db['ts_naive'].dtype == 'datetime64[ns]'
        else:
            assert df_db[col].dtype == 'datetime64[ns, UTC]'
    except ValueError as e:
        print(f"Error with column {col}:", e)
```
```
       ts_utc_winter_summer             ts_diff_offset                 ts_diff_tz            ts_naive
0 2019-11-07 13:37:04+00:00  2019-11-14 16:12:00+01:00  2019-11-14 16:12:00+00:00 2019-11-07 13:37:04
1 2019-08-14 15:12:00+00:00  2019-08-07 15:37:04+02:00  2019-11-14 16:12:00+00:09 2019-08-14 15:12:00 

Error with column ts_diff_offset: Tz-aware datetime.datetime cannot be converted to datetime64 unless utc=True
Error with column ts_diff_tz: Tz-aware datetime.datetime cannot be converted to datetime64 unless utc=True
```

</details>

 I think can just add the new tests to `_TestSQLApi` It seems like local testing with SQL is not possible (it always fails because it won't connect).

But I think I have found the culprit. By adding utc=True in function _parse_date_columns (see comment of @mroeschke [here](https://github.com/pandas-dev/pandas/pull/30492/files/66ebe355c9c080536574ad8c5f0a4a3cb5e5be6b)) we changed the behavior with the argument "parse_dates". See example below:

<details>
<summary>Example</summary>

```python
from sqlalchemy import create_engine
import pandas as pd
import datetime
engine = create_engine('sqlite:///sqlite_test.sqlite3')

# create and save test data
data = [datetime.datetime(2019, 11, 11, 9, 0, 0,  
                          tzinfo=datetime.timezone.utc),
        datetime.datetime(2019, 6, 1, 9, 0, 0, 
                          tzinfo=datetime.timezone.utc)]

df = pd.DataFrame(data, columns = ['ts'])
df.to_sql('test_utc_true', con = engine, index = False, if_exists = 'replace')

# with utc=True change
df = pd.read_sql('SELECT * FROM test_utc_true', 
                 con = engine, 
                 parse_dates = ['ts'])
print(df,'\n')
print(df.dtypes)
```
```
                         ts
0 2019-11-11 09:00:00+00:00
1 2019-06-01 09:00:00+00:00 

ts    datetime64[ns, UTC]
dtype: object
```
```python
# without utc=True change
df = pd.read_sql('SELECT * FROM test_utc_true', 
                 con = engine, 
                 parse_dates = ['ts'])
print(df,'\n')
print(df.dtypes)
```
```
                   ts
0 2019-11-11 09:00:00
1 2019-06-01 09:00:00 

ts    datetime64[ns]
dtype: object
```

</details>

With the consideration from above I made a separate block for the columns in parse_dates in a new commit. @jreback should I rename the title of the PR to something like:

"BUG: sql different offsets (1/2 modify infer_dtype)"

then I can call the next PR:

"BUG: sql different offsets (2/2 modify sql.py)"

Also I think whatsnew entry does not apply for this PR right? infer_dtype is not part of the public API. Is there something else to do from my part for this PR? > @jreback should I rename the title of the PR to something like:
> 
> "BUG: sql different offsets (1/2 modify infer_dtype)"
> 
> then I can call the next PR:
> 
> "BUG: sql different offsets (2/2 modify sql.py)"
> 
> Also I think whatsnew entry does not apply for this PR right? infer_dtype is not part of the public API. Is there something else to do from my part for this PR?

yes pls rename this one and *only* include infer_dtype changes Hey, I was wondering why my PR has not been merged into master yet. I don't mean to rush anything but if there is anything else I have to do please tell me :+1:.
I suppose everyone is very busy with version 1 which is very cool :rocket:. merge master

we have so many PRs if u want a look you can ping (like u did) I merged master again (commit id ca3bfcc54676b04ac5150c913328d26920bb2591) and cythonized again so everything should be up to date. I don't understand why I had an issue with two of the CI tests. Seems to be related to tab completion. I will investigate but I don't really see any relation with what I did... Also @jreback I am leaving you some notes on your changes proposals.

EDIT: jreback I am not sure what you meant with "grep the codebase for infer_dtype...". You wanted me to update the code right? Tab completion is unrelated, don’t worry about it

On Sun, Jan 26, 2020 at 12:09 PM ThibTrip <notifications@github.com> wrote:

> I merged master again (commit id ca3bfcc
> <https://github.com/pandas-dev/pandas/commit/ca3bfcc54676b04ac5150c913328d26920bb2591>)
> and cythonized again so everything should be up to date. I don't understand
> why I had an issue with two of the CI tests. Seems to be related to tab
> completion. I will investigate but I don't really see any relation with
> what I did... Also @jreback <https://github.com/jreback> I am leaving you
> some notes on your changes proposals.
>
> —
> You are receiving this because you were mentioned.
> Reply to this email directly, view it on GitHub
> <https://github.com/pandas-dev/pandas/pull/30492?email_source=notifications&email_token=AB5UM6HKGK646SN74HKZAMTQ7XUXPA5CNFSM4J7PKTG2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEJ54UIA#issuecomment-578538016>,
> or unsubscribe
> <https://github.com/notifications/unsubscribe-auth/AB5UM6DIQSRRVMB2TZPLU5LQ7XUXPANCNFSM4J7PKTGQ>
> .
>
 So I realized one check did not seem necessary as I was able to pass all my tests (plus my other tests I already showed you with psycopg2) and pandas tests (I mean apart from the tab completion and also I made a bad indentation somewhere it seems) without it. So I just removed it. I think I added it to fix something then realized the trigger was elsewhere and then forgot about it.

I also created and used a function to check for datetimetz with nan (got inspired by the integer na funtion). I hope this will seem better to you because I am running out of ideas 😞 Hey, I wanted to say thanks for your patience jreback. 

This PR has been and is quite difficult for me and if anyone wants to help in my branch or even take over I do not mind at all. Not that I want to give up or anything this is actually very interesting!

Anyhow after testing minutely the sql.py module for the failures of the last CI run I can confirm that we need to check if the infered col_type is equal to "datetimetz" in method **SQLTable._sqlalchemy_type** otherwise **test_sqlalchemy_type_mapping** and **test_datetime_with_timezone_roundtrip** (both in pandas/tests/io/test_sql.py) will fail with an sqlalchemy engine connected to a SQLite or postgres database (because the sql dtype of the table model will be Text instead of TIMESTAMP).

So basically if I readd this change to the sql.py module the tests should pass.

However the other bit of code I changed in SQLiteTable._sql_type_name where I also check if the infered col_type is equal to "datetimetz" does not seem to be necessary to pass tests. But it would not give the expected data type in the table model so it seems logical to me to do this change as well (see SQLite test details).

<details>

<summary>SQLite test</summary>

```python
import sqlite3
from pandas import DataFrame, to_datetime, date_range
from pandas.io import sql
from sqlalchemy import create_engine

# with engine = sqlite3.connect(":memory:") we get the same output
engine = create_engine('sqlite:///:memory:') 
db = sql.SQLDatabase(engine)
expected = DataFrame({"A": date_range("2013-01-01 09:00:00", 
                                      periods=3, tz="US/Pacific")})
table = sql.SQLiteTable("test_type", db, frame=expected)
# if we do not check for "datetimetz" in SQLiteTable._sql_type_name
# we end up with "A" as "TEXT" instead of "TIMESTAMP" (see table below)
table.table 
```

```python
['CREATE TABLE "test_type" (\n"index" INTEGER,\n  "A" TEXT \n)',
 'CREATE INDEX "ix_test_type_index"ON "test_type" ("index")']
```


</details>

How shall I proceed? Thanks in advance. Hi @ThibTrip.

Sorry your PR had went unnoticed for a good period of time. Do you have time to merge master?

I suspect we need to add that `col_name == 'datetimetz'` check somewhere.  Hi @mroeschke! Not a problem at all! Also sorry I took so long to reply I have been very busy with work and personal projects. I have merged master. I had a check for datetimetz but I removed it (we need it at the very least here: https://github.com/pandas-dev/pandas/blob/master/pandas/io/sql.py#L973) because the idea IIRC was to touch as little files as possible and do the change in two PRs (in this one we would only make sure infer dtypes recognizes we are in presence of datetime with timezone).
I am going to add it back in another commit, test what I can locally (sadly I never figured out how to test SQL locally) and then we'll see.

 @mroeschke I managed to test SQL locally :+1: ! It was not complicated at all I just did not have sufficient knowledge back when I started this PR. I just needed to install postgres and create a database pandas_nosetest (in the tests it connects with the default postgres user on localhost).

Anyhow I have lots of errors locally that I did not have before and in other tests modules so I am rather surprised. Some tests like pandas.tests.plotting.test_datetimelike.TestTSPlot seem completely irrelevant to my PR yet still fail. So I pushed to see if I have the same errors in the CI tools.

EDIT:

So I added the column type checks I mentionned earlier (see commit 6f9e8ab) as they are necessary to pass the tests. @jreback I think you mentioned the section about DatetimeTZDtype detection before and I did not know how to place it otherwise back then. Sorry about that. I think I know now what you had in mind and have pushed a new commit. Please tell me if that's good like this :+1: .  Just one comment on the tests. The changes to `sql.py` look good.

I'll defer to  @jreback on `infer_dtype`. I think these changes are nice, but closing as stale. ping / open a new PR if you want to continue