BUG: formating integers datetimes using sql GH17855
State: closed

- [x] closes #17855
- [x] tests passed
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [ ] whatsnew entry


Labels:
name: Bug
name: Datetime
description: Datetime data dtype
name: IO SQL
description: to_sql, read_sql, read_sql_query

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

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

```diff
@@            Coverage Diff             @@
##           master   #17882      +/-   ##
==========================================
- Coverage   91.23%   91.21%   -0.02%     
==========================================
  Files         163      163              
  Lines       50102    50104       +2     
==========================================
- Hits        45712    45704       -8     
- Misses       4390     4400      +10
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `89.02% <0%> (-0.01%)` | :arrow_down: |
| #single | `40.31% <66.66%> (-0.06%)` | :arrow_down: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/17882?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/io/sql.py](https://codecov.io/gh/pandas-dev/pandas/pull/17882?src=pr&el=tree#diff-cGFuZGFzL2lvL3NxbC5weQ==) | `94.66% <66.66%> (-0.15%)` | :arrow_down: |
| [pandas/io/gbq.py](https://codecov.io/gh/pandas-dev/pandas/pull/17882?src=pr&el=tree#diff-cGFuZGFzL2lvL2dicS5weQ==) | `25% <0%> (-58.34%)` | :arrow_down: |
| [pandas/core/frame.py](https://codecov.io/gh/pandas-dev/pandas/pull/17882?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZnJhbWUucHk=) | `97.75% <0%> (-0.1%)` | :arrow_down: |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/17882?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/17882?src=pr&el=footer). Last update [aed9b92...0498dd1](https://codecov.io/gh/pandas-dev/pandas/pull/17882?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/17882?src=pr&el=h1) Report
> Merging [#17882](https://codecov.io/gh/pandas-dev/pandas/pull/17882?src=pr&el=desc) into [master](https://codecov.io/gh/pandas-dev/pandas/commit/509e03c6b113e78935ae46707572e4bf7f7a1df5?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/17882/graphs/tree.svg?height=150&width=650&token=eZ4WkYLtcO&src=pr)](https://codecov.io/gh/pandas-dev/pandas/pull/17882?src=pr&el=tree)

```diff
@@            Coverage Diff             @@
##           master   #17882      +/-   ##
==========================================
- Coverage   91.36%   91.34%   -0.02%     
==========================================
  Files         164      164              
  Lines       49730    49732       +2     
==========================================
- Hits        45435    45429       -6     
- Misses       4295     4303       +8
```

| Flag | Coverage Δ | |
|---|---|---|
| #multiple | `89.14% <0%> (ø)` | :arrow_up: |
| #single | `39.65% <100%> (-0.04%)` | :arrow_down: |

| [Impacted Files](https://codecov.io/gh/pandas-dev/pandas/pull/17882?src=pr&el=tree) | Coverage Δ | |
|---|---|---|
| [pandas/io/sql.py](https://codecov.io/gh/pandas-dev/pandas/pull/17882?src=pr&el=tree#diff-cGFuZGFzL2lvL3NxbC5weQ==) | `94.79% <100%> (-0.01%)` | :arrow_down: |
| [pandas/io/gbq.py](https://codecov.io/gh/pandas-dev/pandas/pull/17882?src=pr&el=tree#diff-cGFuZGFzL2lvL2dicS5weQ==) | `25% <0%> (-58.34%)` | :arrow_down: |
| [pandas/core/frame.py](https://codecov.io/gh/pandas-dev/pandas/pull/17882?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvZnJhbWUucHk=) | `97.8% <0%> (-0.1%)` | :arrow_down: |
| [pandas/core/resample.py](https://codecov.io/gh/pandas-dev/pandas/pull/17882?src=pr&el=tree#diff-cGFuZGFzL2NvcmUvcmVzYW1wbGUucHk=) | `96.34% <0%> (+0.18%)` | :arrow_up: |

------

[Continue to review full report at Codecov](https://codecov.io/gh/pandas-dev/pandas/pull/17882?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/17882?src=pr&el=footer). Last update [509e03c...987f588](https://codecov.io/gh/pandas-dev/pandas/pull/17882?src=pr&el=lastupdated). Read the [comment docs](https://docs.codecov.io/docs/pull-request-comments).

@drorata : Will need to add tests for the new behavior.
can you rebase and update according to comments
@jreback It's on my agenda and I'll do it as soon as I have time.
@drorata there went something wrong with updating the branch (see all the commits included here on github). 
To fix this, doing exactly the following should normally work (assuming 'usptream' is pandas-dev/pandas, and 'origin' is drorata/pandas):

```
git pull upstream master
git push origin fix-17882 -f
```

That worked! 
Can you further add some tests for this? (and update for my comment above? https://github.com/pandas-dev/pandas/pull/17882#discussion_r144784251)
I guess before merging I still have to squash all commits, is that correct? What is the canonical way to do so?
> I guess before merging I still have to squash all commits, is that correct? What is the canonical way to do so?

No need to squash in the PR. We will squash when merging (github provides that), so you can keep the history in the PR branch itself as it is.
Seems like some tests fail on ci/circleci. Why is it? Locally everything passes (or skipped which I assumed is OK)
The error on circle ci seems relevant, as something in the setup of sql tests is failing
The log says there is a sql syntax error:

```
E       sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'"IntDateOnlyCol" INTEGER,\n                    `FloatCol` DOUBLE,\n               \' at line 5') [SQL: 'CREATE TABLE types_test_data (\n                    `TextCol` TEXT,\n                    `DateCol` DATETIME,\n                    `IntDateCol` INTEGER,\n                    "IntDateOnlyCol" INTEGER,\n                    `FloatCol` DOUBLE,\n                    `IntCol` INTEGER,\n                    `BoolCol` BOOLEAN,\n                    `IntColWithNull` INTEGER,\n                    `BoolColWithNull` BOOLEAN\n                )']

../miniconda3/envs/pandas/lib/python3.6/site-packages/pymysql/err.py:107: ProgrammingError
```
The `travis` test failed due to timeout ([link](https://travis-ci.org/pandas-dev/pandas/jobs/305285071#L5247)). Any idea? @jorisvandenbossche @jreback 
I restarted the build. All builds seemed rather slow. I don't directly see something in the diff here that could cause a slowdown, so maybe it is a travis issue. Let's see when it finishes.
Who can help me review the fail of `AppVeyor`'s build?
It was also something unrelated I think (it couldn't reach github), so restarted the build
@gfyoung are you comfortable with this PR?
It seems that finally everything is green! (not sure why the CIs were so flaky here)
@drorata Thanks for the fix!
Ah, forgot to ask you to add a whatsnew, did that in https://github.com/pandas-dev/pandas/commit/cf909957dae0a07fcb00379aeb2ed9323cd888ff