BUG: _convert_and_box_cache raise ValueError: Tz-aware datetime.datetime cannot be converted to datetime64 unless utc=True
State: closed

- [x] closes #N/A
- [x] tests added / passed
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry

Bug was found there: https://github.com/pandas-dev/pandas/pull/26043#issuecomment-483214458
Reproduction:
```python
import pandas as pd
N = 10
half = int(N / 2)
ts_string_1 = 'March 1, 2018 12:00:00+0400'
ts_string_2 = 'March 1, 2018 12:00:00+0500'
diff_offset = [ts_string_1] * half + [ts_string_2] * half
pd.to_datetime(diff_offset, cache=True)
```

Labels:
name: Datetime
description: Datetime data dtype

Comments:
Hello @anmyachev! Thanks for updating this PR. We checked the lines you've touched for [PEP 8](https://www.python.org/dev/peps/pep-0008) issues, and found:







There are currently no PEP 8 issues detected in this Pull Request. Cheers! :beers: 

##### Comment last updated at 2019-07-03 11:44:41 UTC
always tests first!
# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/26097?src=pr&el=h1) Report
> Merging [#26097](https://codecov.io/gh/pandas-dev/pandas/pull/26097?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/23966edf1dede0cd8a0621509c713c27344273ae?src=pr&el=desc) will **decrease** coverage by `<.01%`.
> The diff coverage is `100%`.

[![Impacted file tree graph](https://codecov.io/gh/pandas-dev/pandas/pull/26097/graphs/tree.svg?width=650&token=eZ4WkYLtcO&height=150&src=pr)](https://codecov.io/gh/pandas-dev/pandas/pull/26097?src=pr&el=tree)

```diff
@@            Coverage Diff             @@
##           master   #26097      +/-   ##
==========================================
- Coverage   91.96%   91.95%   -0.01%     
==========================================
  Files         175      175              
  Lines       52412    52414       +2     
==========================================
- Hits        48199    48197       -2     
- Misses       4213     4217       +4
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `90.51% <100%> (ø)` | :arrow_up: |
| #single | `40.73% <0%> (-0.14%)` | :arrow_down: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/26097?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/core/tools/datetimes.py](https://codecov.io/gh/pandas-dev/pandas/pull/26097/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvdG9vbHMvZGF0ZXRpbWVzLnB5) | `84.68% <100%> (+0.08%)` | :arrow_up: |
| [pandas/io/gbq.py](https://codecov.io/gh/pandas-dev/pandas/pull/26097/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2dicS5weQ==) | `75% <0%> (-12.5%)` | :arrow_down: |
| [pandas/core/frame.py](https://codecov.io/gh/pandas-dev/pandas/pull/26097/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZnJhbWUucHk=) | `96.9% <0%> (-0.12%)` | :arrow_down: |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/26097?src=pr&el=continue).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/26097?src=pr&el=footer). Last update [23966ed...b725de6](https://codecov.io/gh/pandas-dev/pandas/pull/26097?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

# [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/26097?src=pr&el=h1) Report
> Merging [#26097](https://codecov.io/gh/pandas-dev/pandas/pull/26097?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/7ec7c9eecab5720a88e659fd0993eaab47d0c33b?src=pr&el=desc) will **decrease** coverage by `0.01%`.
> The diff coverage is `100%`.

[![Impacted file tree graph](https://codecov.io/gh/pandas-dev/pandas/pull/26097/graphs/tree.svg?width=650&token=eZ4WkYLtcO&height=150&src=pr)](https://codecov.io/gh/pandas-dev/pandas/pull/26097?src=pr&el=tree)

```diff
@@            Coverage Diff             @@
##           master   #26097      +/-   ##
==========================================
- Coverage   91.87%   91.86%   -0.02%     
==========================================
  Files         180      180              
  Lines       50927    50714     -213     
==========================================
- Hits        46790    46589     -201     
+ Misses       4137     4125      -12
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `90.46% <100%> (-0.11%)` | :arrow_down: |
| #single | `41.13% <73.68%> (-0.96%)` | :arrow_down: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/26097?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/core/index.py](https://codecov.io/gh/pandas-dev/pandas/pull/26097/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvaW5kZXgucHk=) | `100% <100%> (ø)` | :arrow_up: |
| [pandas/core/tools/datetimes.py](https://codecov.io/gh/pandas-dev/pandas/pull/26097/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvdG9vbHMvZGF0ZXRpbWVzLnB5) | `85.22% <100%> (+0.16%)` | :arrow_up: |
| [pandas/tseries/converter.py](https://codecov.io/gh/pandas-dev/pandas/pull/26097/diff?src=pr&el=tree#diff-cGFuZGFzL3RzZXJpZXMvY29udmVydGVyLnB5) | `0% <0%> (-100%)` | :arrow_down: |
| [pandas/io/gbq.py](https://codecov.io/gh/pandas-dev/pandas/pull/26097/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2dicS5weQ==) | `88.88% <0%> (-11.12%)` | :arrow_down: |
| [pandas/plotting/\_misc.py](https://codecov.io/gh/pandas-dev/pandas/pull/26097/diff?src=pr&el=tree#diff-cGFuZGFzL3Bsb3R0aW5nL19taXNjLnB5) | `59.49% <0%> (-5.38%)` | :arrow_down: |
| [pandas/plotting/\_matplotlib/converter.py](https://codecov.io/gh/pandas-dev/pandas/pull/26097/diff?src=pr&el=tree#diff-cGFuZGFzL3Bsb3R0aW5nL19tYXRwbG90bGliL2NvbnZlcnRlci5weQ==) | `58.43% <0%> (-5.24%)` | :arrow_down: |
| [pandas/io/excel/\_openpyxl.py](https://codecov.io/gh/pandas-dev/pandas/pull/26097/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2V4Y2VsL19vcGVucHl4bC5weQ==) | `84.71% <0%> (-3.23%)` | :arrow_down: |
| [pandas/core/config\_init.py](https://codecov.io/gh/pandas-dev/pandas/pull/26097/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvY29uZmlnX2luaXQucHk=) | `92.91% <0%> (-3.17%)` | :arrow_down: |
| [pandas/io/formats/printing.py](https://codecov.io/gh/pandas-dev/pandas/pull/26097/diff?src=pr&el=tree#diff-cGFuZGFzL2lvL2Zvcm1hdHMvcHJpbnRpbmcucHk=) | `85.56% <0%> (-1.65%)` | :arrow_down: |
| [pandas/core/internals/managers.py](https://codecov.io/gh/pandas-dev/pandas/pull/26097/diff?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvaW50ZXJuYWxzL21hbmFnZXJzLnB5) | `95.21% <0%> (-0.95%)` | :arrow_down: |
| ... and [75 more](https://codecov.io/gh/pandas-dev/pandas/pull/26097/diff?src=pr&el=tree-more) | |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/26097?src=pr&el=continue).
> **Legend** - [Click here to learn more](https://docs.codecov.io/docs/codecov-delta)
> `Δ = absolute <relative> (impact)`, `ø = not affected`, `? = missing data`
> Powered by [Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/26097?src=pr&el=footer). Last update [7ec7c9e...1cc469e](https://codecov.io/gh/pandas-dev/pandas/pull/26097?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

@mroeschke we've replaced bandaid fix with the one that applies same boxing logic whenever caching is on or off.

@jreback we've added a test which checks this case for both `cache=True` and `cache=False` (and which fails prior to our fix).

whatsnew entry will be added a bit later.
Could you describe your fix? Was it not boxing in the correct location or was the error parameter inconsistent?
> Could you describe your fix?

There was different boxing logic for `cache=True` and `cache=False`:

* In `cache=False` case it boxed to `DatetimeIndex` when it was possible to box to it (regardless of what `errors` was), otherwise it boxed to `Index`.
* In `cache=True` case it boxed to `DatetimeIndex` when `errors` was `ignore` only (regardless of whether it was possible to box to `DatetimeIndex`).

So the fix is to align the boxing logic for both `cache=True` and `cache=False` cases by using same boxing method.
Note to reviewers: I've rebased off `master` to get rid of testing failures that were fixed in master after this PR was initially made.
Current failures seem to be introduced by rebasing on `master`, see #26187 and #26189
Another example of `to_datetime` being chaotic:

```
>>> pd.to_datetime(['20100101 0100+1', '20100101 0000+0'],errors='raise',cache=True)
Index([2010-01-01 01:00:00+01:00, 2010-01-01 00:00:00+00:00], dtype='object')
>>> pd.to_datetime(['20100101 0100+1', '20100101 0000+0'],errors='raise',cache=False)
Index([2010-01-01 01:00:00+01:00, 2010-01-01 00:00:00+00:00], dtype='object')
>>> pd.to_datetime(['20100101 0100+1', '20100101 0100+1', '20100101 0000+0'],errors='raise',cache=True)
Traceback (most recent call last):
  File "C:\Vass\Work\cuDF\pandas-dev\pandas\core\arrays\datetimes.py", line 1860, in objects_to_datetime64ns
    values, tz_parsed = conversion.datetime_to_datetime64(data)
  File "pandas\_libs\tslibs\conversion.pyx", line 185, in pandas._libs.tslibs.conversion.datetime_to_datetime64
    raise ValueError('Array must be all same time zone')
ValueError: Array must be all same time zone
>>> pd.to_datetime(['20100101 0100+1', '20100101 0100+1', '20100101 0000+0'],errors='raise',cache=False)
Index([2010-01-01 01:00:00+01:00, 2010-01-01 01:00:00+01:00,
       2010-01-01 00:00:00+00:00],
      dtype='object')
```

I'm starting to feel that this discussion (and the test I've added) is unearthing something that is much bigger than initial error reported.
can you merge master and update
can you merge master, some checks are failing
@anmyachev can you merge master and see if can get passing
can you merge master
@jreback CI build errors are not mine
@jreback ping on green
@anmyachev this all looks good, but this failed

```
=================================== FAILURES ===================================
___ TestToDatetime.test_to_datetime_cache_invariance[<lambda>-suffix0-raise] ___
[gw1] linux -- Python 3.6.6 /home/travis/miniconda3/envs/pandas-dev/bin/python
self = <pandas.tests.indexes.datetimes.test_tools.TestToDatetime object at 0x7fa4aff66828>
suffix = [], errors = 'raise'
convertor = <function TestToDatetime.<lambda> at 0x7fa4e0778268>
    @pytest.mark.parametrize('errors', ('ignore', 'coerce', 'raise'))
>   @pytest.mark.parametrize('suffix', ([], ['foo']))
    @pytest.mark.parametrize('convertor', (lambda x: x, str))
    @given(date1=datetimes(timezones=timezones()),
           date2=datetimes(timezones=timezones()))
    def test_to_datetime_cache_invariance(self, date1, date2, suffix,
                                          errors, convertor):
pandas/tests/indexes/datetimes/test_tools.py:524: 
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
pandas/tests/indexes/datetimes/test_tools.py:542: in test_to_datetime_cache_invariance
    tm.assert_almost_equal(cache_on, cache_off)
pandas/util/testing.py:289: in assert_almost_equal
    **kwargs)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ 
left = DatetimeIndex(['2000-01-01 00:00:00+00:00', '2000-01-01 00:00:00+00:00',
               '2000-01-01 00:00:00+00:00', '...       '2000-01-01 00:00:00+00:00', '2000-01-01 00:00:00+00:00'],
              dtype='datetime64[ns, UTC]', freq=None)
right = ('Tz-aware datetime.datetime cannot be converted to datetime64 unless utc=True',)
cls = <class 'pandas.core.indexes.base.Index'>
    def _check_isinstance(left, right, cls):
        """
        Helper method for our assert_* methods that ensures that
        the two objects being compared have the right type before
        proceeding with the comparison.
    
        Parameters
        ----------
        left : The first object being compared.
        right : The second object being compared.
        cls : The class type to check against.
    
        Raises
        ------
        AssertionError : Either `left` or `right` is not an instance of `cls`.
        """
    
        err_msg = "{name} Expected type {exp_type}, found {act_type} instead"
        cls_name = cls.__name__
    
        if not isinstance(left, cls):
            raise AssertionError(err_msg.format(name=cls_name, exp_type=cls,
                                                act_type=type(left)))
        if not isinstance(right, cls):
            raise AssertionError(err_msg.format(name=cls_name, exp_type=cls,
>                                               act_type=type(right)))
E           AssertionError: Index Expected type <class 'pandas.core.indexes.base.Index'>, found <class 'tuple'> instead
```
@jreback seems that I fixed problem. 
When defining unique elements, the following dates were considered duplicates:
```
>>> dt1 = datetime.datetime(2000, 1, 1, 0, 0, tzinfo=pytz.timezone('UTC'))
>>> dt2 = datetime.datetime(2000, 1, 1, 0, 0, tzinfo=pytz.timezone('Etc/GMT'))
>>> dt2 = datetime.datetime(2000, 1, 1, 0, 0, tzinfo=pytz.timezone('Etc/GMT+0'))
>>> dt1 == dt2 == dt3
True
```
but the time zones of these dates were considered unique. So the extra check that I entered solves the problem.
Remaining error: `AttributeError: module 'numpy.ma' has no attribute 'mrecords'`
@anmyachev if you can merge master as well
can you merge master
@jreback I want to move `test_to_datetime_cache_invariance` and everything connected with it to another PR. Do you agree?
sure
@jreback ping on green
lgtm. ping on green.
@jreback ping on green
thanks @anmyachev