Add support for DATETIME BigQuery type

[WillFuks]

Recently a question was posted on Stackoverflow regarding support for DATETIME field type in BigQuery. I was wondering if it's possible to update the mapping that the method to_gbc does to also map DATETIME values.

Thanks!

[tswast]

I made a change over at the google-cloud-python libraries do some type inference for the DB-API interface. https://github.com/tswast/google-cloud-python/blob/3b7c505e39d5091db5f3d618f181886189c362b7/bigquery/google/cloud/bigquery/dbapi/_helpers.py#L64

pandas-gbq probably needs similar logic to disambiguate.

[max-sixty]

The only differentiation we could do without schema input (ref #46) would be datetime / timestamp based on tz.

Although if anyone wants to add Period support, which can represent date 😚 (though I don't think Period is widely used enough for anyone else to spend the time)

[Sherm4nLC]

I believe that you might want to try the data type TIMESTAMP instead.

[tswast]

The PR at #25 uses the google-cloud-bigquery library to run the query and fetch the results. That library turns a DATETIME into a datetime object without a time zone. @Sherm4nLC is correct that in most cases TIMESTAMP is actually what is desired.

[tswast]

Closing since I believe this is fixed by #25

[tswast]

Re-opening as I noticed DATETIME is not one of the types in _generate_bq_schema, used by to_gbq.

[max-sixty]

This would need to be supplied manually because there is no differentiation between these in native pandas types

[Sherm4nLC]

I believe that when datetime64[ns] has time 00:00:00 in all rows, it gets rounded and then cannot work with TIMESTAMP either. I worked around this by adding 1 ms.

[max-sixty]

I also noticed that when pulling a column with date type, we get an object index that contains dates:

a query with:

  cast(date as timestamp) as date_as_ts,
  date,

generates a table with dtypes:

date_as_ts        datetime64[ns]
date                      object

And takes up 8x the memory

sys.getsizeof(df['date_as_ts'])
861048

sys.getsizeof(df['date'])
4304824